尝试使用几天前在这里收到的一些建议来重做我几天前做的函数...我在Debug.Print的行上收到一个无效的过程调用或参数。
VBA和Excel的新手。感谢您的帮助和耐心等待。
Public Sub GetSOPFiles()
' Set folder path
Const FolderPath As String = "C:\Users\test\Desktop\SOP Excel Prototype"
Const FileExt As String = "docx"
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Dim dept As Variant
Dim deptCodes As Variant
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
Dim vData As Variant
Dim sTemp As Variant
ReDim vData(0 To MyFiles.Count - 1, 0 To 0)
x = 1
With MyFiles
While x <= UBound(vData, 1)
Debug.Print (MyFiles(x).Name)
Wend
End With
End Sub
MyFiles
是一个集合,应使用For Each...Next
循环对其进行迭代。
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
For Each MyFile In MyFiles
Debug.Print MyFile.Name ' no parentheses
Next
您无法索引到MyFiles
集合中。For Each...Next
如Files
文档所述,使用循环。
是的,我原来有这样的文件。有人建议With / End语句要比For Each语句快。你怎么看?
With...End With
不是循环。While...Wend
已或多或少不推荐使用。这是For...Each
循环的简单情况。如果您担心速度,请阅读analyticscave.com/vba-for-loop-vs-for-each-loop。还有一点-即使
MyFiles(x)
是有效的语法,您当前也有一个无限循环...