Trying to redo a function I made a few days ago using some advice I received on here...I get an Invalid Procedure call or argument on the line with Debug.Print.
Very new to VBA and Excel. Thanks for your help and patience.
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
is a collection and should be iterated over with a For Each...Next
loop.
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
You can't index into the MyFiles
collection. Use a For Each...Next
loop, as the Files
documentation explains.
Yes, I had my file like that originally. Someone suggested that a With/End statement would be faster than a For Each. What are your thoughts?
With...End With
is not a loop.While...Wend
is more or less deprecated. This is a simple case forFor...Each
loop. If you're concerned about speed, perhaps read analystcave.com/vba-for-loop-vs-for-each-loop.As another point - even if
MyFiles(x)
were valid syntax, you currently have an infinite loop...