1

So I'm pretty new to VBA.

The below code works fine in 2007 for listing all of the PDF files in a particular folder. However, this code doesn't seem to work when I try it in excel 2010 (it throws an error on Set fold = fso.GetFolder(folderPath))

Any Ideas What I'm doing wrong?

I do have Scripting Runtime checked. My code is below:

Sub List_files()

Dim fso As FileSystemObject
Dim fold As Folder
Dim f As File
Dim folderPath As String
Dim i As Integer

folderPath = "S:\Academic Affairs\Academic Operations Reporting\CV's"
Set fso = New FileSystemObject
Set fold = fso.GetFolder(folderPath)

i = 2
For Each f In fold.Files
    If LCase(Right(f.Name, 3)) = "pdf" Then
        Range("A" & i).Value = f.Name
        i = i + 1
    End If
Next

End Sub
Community
  • 1
  • 1
user2653858
  • 41
  • 1
  • 2
  • 4

5 Answers5

2

I think you need a "\" on the folderPath variable... so that it is

folderPath = "S:\Academic Affairs\Academic Operations Reporting\CV's\"

If that doesn't fix it, post the error you're getting.

Eric
  • 145
  • 1
  • 1
  • 9
  • I added the \ and it didn't fix it. Run time error 76, file path not found. I've checked the name a dozen times. There's about a dozen users using this program in excel 2007 and they're not receiving the error, but I do on 2010 – user2653858 Aug 06 '13 at 17:00
  • Sorry guys, I figured it out. I don't know why but it appears that the name of my S:\ drive is "Academic Affairs" and in 2010 it doesn't let me express it this way: "S:\Academic Affairs\Academic Operations Reporting\CV's" but now works when I express it this way: ""S:\Academic Operations Reporting\CV's. However, 2007 works just fine with the original method. – user2653858 Aug 06 '13 at 19:34
  • If you have spaces in file/folder names it's often necessary to escape them with an extra set of quotes. see http://stackoverflow.com/questions/4835691/escape-double-quote-in-vb-string I prefer to use underscores _ instead of spaces as it's more robust – user1515373 Feb 12 '15 at 21:35
  • Original spelling of your path works in 2016. Also note that apostrophes are interpreted as comments in VBA –  Feb 21 '18 at 16:57
2

Here is a procedure that I use for listing files:

Function GetFileList(pDirPath As String) As Variant
On Error GoTo GetFileList_err

    ' Local constants / variables
    Const cProcName = "GetFileList"
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim c As Double           ' upper bound for file name array
    Dim i As Double           ' iterator for file name array
    Dim vFileList() As String ' array for file names

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(pDirPath)
    c = objFolder.Files.Count
    i = 0

    ReDim vFileList(1 To c)  ' set bounds on file array now we know count

    'Loop through the Files collection
    For Each objFile In objFolder.Files
        'Debug.Print objFile.Name
        i = i + 1
        vFileList(i) = objFile.Name
    Next

    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

    GetFileList = vFileList

GetFileList_exit:
    Exit Function

GetFileList_err:
    Debug.Print "Error in ", cProcName, " Err no: ", Err.Number, vbCrLf, "Err Description: ", Err.Description
    Resume Next

End Function

Sub PrintFileList(pDirPath As String, _
                  Optional pPrintToSheet = False, _
                  Optional pStartCellAddr = "$A$1", _
                  Optional pCheckCondition = False, _
                  Optional pFileNameContains)
On Error GoTo PrintFileList_err

    ' Local constants / variables
    Const cProcName = "PrintFileList"
    Dim vFileList() As String ' array for file names
    Dim i As Integer          ' iterator for file name array
    Dim j As Integer          ' match counter
    Dim c As String

    vFileList = GetFileList(pDirPath)
    c = pStartCellAddr
    j = 0

    For i = LBound(vFileList) To UBound(vFileList)
        If pPrintToSheet Then
            If pCheckCondition Then
                ' if pFileNameContains not in filename go to next iteration of loop
                If InStr(1, vFileList(i), pFileNameContains, vbTextCompare) = 0 Then
                    GoTo EndLoop
                End If
            End If
            Range(c).Offset(j, 0).Value = vFileList(i)
            j = j + 1
        End If
        'Debug.Print vFileList(i)
        i = i + 1
EndLoop:
    Next

PrintFileList_exit:
    Exit Sub

PrintFileList_err:
    Debug.Print "Error in ", cProcName, vbCrLf, "Err no: ", Err.Number, _
                vbCrLf, "Err Description: ", Err.Description
    Resume Next

End Sub

The function is just for internal use, you call the procedure. Here is an example call (in this case using the userprofile windows environment variable as the path rather than a hard coded path):

call PrintFileList(environ("userprofile"), True, "$A$1", True, ".pdf")
ChrisProsser
  • 12,598
  • 6
  • 35
  • 44
2

Whenever things are not working as they "should" it's very productive to start with a minimal approach that works and build from there. Try this that works in Excel 2016:

Option Explicit

Sub File_renaming2()
    Dim objFSO As FileSystemObject
    Dim mySource As Folder
    Dim myFolder As File

    Set objFSO = New FileSystemObject
    Set mySource = objFSO.GetFolder("S:\Academic Affairs\Academic Operations Reporting\CV's\")
    For Each myFolder In mySource.Files
        Debug.Print myFolder.Name
    Next myFolder
End Sub
1

Use this:

Set fso = New Scripting.FileSystemObject
belwood
  • 3,320
  • 11
  • 38
  • 45
coder2448
  • 11
  • 1
1

Don't know how to explain: But we need to make the full reference to the object type

CHANGE                            
    "Dim mySource As Folder "         
TO
    "Dim mySource As Scripting.Folder"    'OR "Dim mySource As object"

Why ? In my case the working code stopt from working => I added the "microsoft outlook object library" => it has a "Folder" type to => so nothing worked for me aftherwards

user12847642
  • 11
  • 2