List all files in directory in an Excel file

Good morning, Gurus.

In my search I found several example similar to what I need, but nothing I could adapt to filt my needs, (at least not wim my limited skills).

Here what I need to do:
I have multilple Excel files in a directory. (M:/Archived PO Responses/Domestic). On a daily basis, these files are processed via VBA, and deleted after processing. What I need to do is, prior to processing and deleting these files, create an ongoing log of the filenames in that directory.

Example:
The macro would open an Excel file named "Processed Orders.xls", which is stored on the network drive "M:". It would then append all of the filenames in the directory mentioned above to a sheet named "Processed Orders" in that workbook, below any filenames that already exist.

Thanks in advance for any help you can offer.

Hello Matt,

Here is a macro I wrote a little while ago because I needed to keep better track of my files. You can select the file type to list and do wild card matching on the file names. Copy this code into a standard module in the workbook "Processed Orders.xls". Add a button to the worksheet "Processed Orders" and attach the macro "ListFiles" to it. Here is the code...

Please Login or Register  to view this content.

Hello Hutch,

I'll have to double check the macro. I don't see anything obvious in the code and thought it listed all the files in the tests. If the same result is happening on 2 directories then something is not right. I'll look into it.

Hello Hutch,

Found the problem. Using arrays can be confusing. Sometimes they are zero based, and other times one based.

Please Login or Register  to view this content.