Excel VBA FSO.GetFolder(folderPath) 在 2007 年工作但不是 2010 年

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

提问by user2653858

So I'm pretty new to VBA.

所以我对VBA很陌生。

The below code works fine in 2007 for listing all of the PDF filesin 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))

下面的代码在 2007 年可以正常工作，用于列出PDF files特定文件夹中的所有内容。但是，当我在 excel 2010 中尝试时，此代码似乎不起作用（它在 上引发错误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

回答by Eric

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

我认为您需要在 folderPath 变量上添加一个“\”...

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

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

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

如果这不能解决问题，请发布您遇到的错误。

回答by ChrisProsser

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", _
                  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):

该函数仅供内部使用，您调用该过程。这是一个示例调用（在这种情况下，使用 userprofile windows 环境变量作为路径而不是硬编码路径）：

call PrintFileList(environ("userprofile"), True, "$A", True, ".pdf")

回答by ChrisProsser

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:

每当事情没有像他们“应该”那样工作时，从最简单的方法开始，从那里开始工作和构建是非常有成效的。试试这个在 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

回答by user12847642

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

为什么 ？在我的情况下，工作代码停止工作 => 我添加了“microsoft Outlook 对象库”=> 它有一个“文件夹”类型 => 所以之后没有任何工作对我有用

回答by coder2448

Use this:

用这个：

Set fso = New Scripting.FileSystemObject