Excel VBA FSO.GetFolder（folderPath）在2007年工作但不是2010年

问题描述

所以我很熟悉VBA。

以下代码在2007年正常运行，用于列出特定文件夹中的所有 PDF文件。但是，当我在excel 2010中尝试这个代码时，这段代码似乎不起作用（它在上设置一个错误Set fold = fso.GetFolder（folderPath））

任何想法我在做错什么？

我有脚本运行时检查。我的代码如下：

  Sub List_files（）
 
 Dim fso As FileSystemObject 
 Dim折叠为文件夹
 Dim f As File 
 Dim folderPath As String 
 Dim i As Integer 
 
 folderPath =S：\Academic Affairs\Academic Operations Reporting \\ CV的
 Set fso = New FileSystemObject 
设置fold = fso.GetFolder（folderPath）
 
i = 2 
对于每个f In fold.Files 
如果LCase（右（f.Name，3））=pdf然后
范围（A& i）.Value = f.Name 
i = i + 1 
结束If 
下一个
 
 End Sub 
  

解决方案

这是一个用于列出文件的过程：

 函数GetFileList（pDirPath As String）As Variant 
错误GoTo GetFileList_err 
 
'本地常量/变量
 Const cProcName =GetFileList
 Dim objFSO As Object 
 Dim objFolder As Object 
 Dim objFile As Object 
 Dim c As文件名数组的双重上限
 Dim i As文件名数组的Double'迭代器
 Dim vFileList（）As String'文件名的数组
 
设置objFSO = CreateObject（Scripting.FileSystemObject）
设置objFolder = objFSO.GetFolder（pDirPath）
c = objFolder.Files.Count 
i = 0 
 
 ReDim vFileList 1 to c）'设置文件数组上的边界现在我们知道count 
 
'循环通过文件集
对于每个objFile在objFolder.Files 
'Debug.Print objFile.Name 
i = i + 1 
 vFileList（i）= objFile.Name 
下一个
 
'清理！ 
 Set objFolder = Nothing 
 Set objFile = Nothing 
 Set objFSO = Nothing 
 
 GetFileList = vFileList 
 
 GetFileList_exit：
 Exit函数
 
 GetFileList_err：
 Debug.PrintError in，cProcName，Err no：，Err.Number，vbCrLf，Err Description：，Err.Description 
 Resume Next 
 
结束函数
 
 Sub PrintFileList（pDirPath As String，_ 
可选pPrintToSheet = False，_ 
可选pStartCellAddr =$ A $ 1 ，_ 
可选pCheckCondition = False，_ 
可选pFileNameContains）
错误GoTo PrintFileList_err 
 
'本地常量/变量
 Const cProcName =PrintFileList 
 Dim vFileList（）As String'用于文件名的数组
 Dim i As文件名数组的整数迭代器
 Dim j As Integer'match counter 
 Dim c As String 
 
 vF ileList = GetFileList（pDirPath）
c = pStartCellAddr 
j = 0 
 
对于i = LBound（vFileList）到UBound（vFileList）
如果pPrintToSheet然后
如果pCheckCondition然后
'如果pFileNameContains不在文件名中转到循环的下一个迭代
如果InStr（1，vFileList（i），pFileNameContains，vbTextCompare）= 0然后
 GoTo EndLoop 
结束如果
 End If 
 Range（c）.Offset（j，0）.Value = vFileList（i）
j = j + 1 
 End If 
'Debug。打印vFileList（i）
i = i + 1 
 EndLoop：
下一个
 
 PrintFileList_exit：
退出子
 
 PrintFileList_err： 
 Debug.PrintError in，cProcName，vbCrLf，Err no：，Err.Number，_ 
 vbCrLf，Err Description：，Err.Description 
 Resume Next 
 
 End Sub 
  

功能是jus t为内部使用，您调用该过程。这里是一个示例调用（在这种情况下使用userprofile Windows环境变量作为路径而不是硬编码路径）：

 调用PrintFileList（environ（userprofile），True，$ A $ 1，True，.pdf）
  

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

解决方案

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

这篇关于Excel VBA FSO.GetFolder（folderPath）在2007年工作但不是2010年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！