file system object error, trying to index a file list object

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



    cheers!
    Nate

  • Re: file system object error, trying to index a file list object


    Hi Jindon,


    Thanks for the reply. I'm seeing the script error out when index = 1 and myFiles.Count = 200 so I don't think it's an index range issue... unfortunately, because your solution would work then!


    Nate

  • Re: file system object error, trying to index a file list object


    OK


    I don't think you can use index for files collection.
    Also you have meaningless lines for Sub procedure, seems Function procedure?


    Not tested.

  • Re: file system object error, trying to index a file list object


    Hi Jindon!


    That makes sense with what I am seeing in the locals window while running this script. Instead of an object with indexes I'm seeing separate entries name "item 1" "item 2" etc. It seems so close but yet so far.


    Also, this is supposed to be a function but I modified the code to be a sub so that the built in error trapping and debug features work. This is my final code:



    cheers!
    Nate

  • Re: file system object error, trying to index a file list object


    OK, so this function works, but it crashes excel due to the slow speed of the loop in each use of the function. (think folder with 200 files) this would be around 30,000 executions.


    Since it is a function it runs each time excel decides to update sheets. We are violating one of the Golden Rules (Don't write functions/sheets that require xlCalculationManual).


    I'm trying to think of a faster way to do this, is there some sort of replacement or workaround for indexing in the files collection?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!