CODEWITHSUNDEEP
excelvba
user2653858
user2653858

Reputation: 41

Excel VBA FSO.GetFolder(folderPath) working in 2007 but not 2010

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

Upvotes: 1

Views: 50035

Answers (5)

user12847642
user12847642

Reputation: 11

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

Upvotes: 1

coder2448
coder2448

Reputation: 11

Use this:

Set fso = New Scripting.FileSystemObject

Upvotes: 1

user2261597
user2261597

Reputation:

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

Upvotes: 2

ChrisProsser
ChrisProsser

Reputation: 13088

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")

Upvotes: 2

Eric
Eric

Reputation: 145

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.

Upvotes: 2

Related Questions