looping macro for all files in a specified folder

Good Morning All,
Still a little new to the excel vba programming but was hoping that someone could assist me in a macro I wish to create. I have a folder containing n number of data files all of the same format and wish to create a macro that will loop through all of the files in the folder and apply the macro listed below.
I have set up the macro below to run through one data file that has been selected by the user and now wish to apply it to all files in said folder. I know there is a loop option but can anyone explain to me how to do this?
Thanks so much,

Mel

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi,
file handling is done by the FileSytemObject class. This is contained in the reference "Microsoft Scripting Runtime" that you'll want to your project (Tools - References). Once you have that you can easily loop through the files:

Please Login or Register  to view this content.

Thank you so much for your quick reply. I am getting a byRef argument type mismatch for the Input_File in the Call Import_PCH_File
How can I address this?

Thank you!!!!!!!

Thank you for your quick response. I will look more into this file system

You can't combine my solution with Carl's.

Using Filesystemobject to loop through the files will return each file as an object (file object to be precise). The function I gave you requried the file as a string. I gave you the macro at the bottom that uses VBA's dir() function which loops through files in a folder but returns the files as a string. This is the one you need to use, or you need to change use the .path/.name properties of the file to get the filename as string. That will address your byref mismatch issue.

In essence the two methods for doing this are:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Note: the second method is similar to what Carl did but with late binding so it does not need the reference to the filesystemobject.