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:
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