Hey gurus,
I'm trying to make a function which will allow me to input a folder and an index in a sheet to quickly list files in my worksheet. I think my error has to do with not understanding the file system object but that's about as far as I can figure. I've modified my code to be a sub so that the error trapping works, I tried to comment and explain where the code will be modified back into a function once I figure out this error.
My code throws a runtime error 5: invalid procedure call or argument on myFiles(index).
here's my code:
Code
Public Sub GETFILENAMES_func() 'fileFolder As String, index As Long)
'for testing
Dim fileFolder As String
Dim index As Long
fileFolder = "D:\Users\ln255\Documents\31121010\tuning_guide"
index = 1
'real function starts here
Dim myFileSys, myFolder, myFiles, thisFile 'file system vars
Set myFileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = myFileSys.getfolder(fileFolder)
Set myFiles = myFolder.files
If myFiles.Count < index Then
GETFILENAMES = CVErr(xlErrNA) 'index error
Exit Sub
End If
Set thisFile = myFiles(index) 'error out here
GETFILENAMES = thisFile.Name
End Sub
Display More
cheers!
Nate