Search folder for excel files.

Hi
I am creating the attached workbook through VBA code on the raw data, and this part works perfectly. providing me with a summary of each day (file)

I wanted to know if there is a way to create a code that would be able to create a master summary workbook.

Basically I would have these daily worksheets created each day by running the macro providing a summary sheet of the data from that day (as per attached file). All of these stored in one folder on my drive.

I would like to be able to have a new master workbook that looks at the folder where these files are stored recognizes when a new file has been added and then copies the data from that summary sheet and pastes that data as new line in the Master Summary worksheet.

I had found some code to open newest files in a folder, but there maybe times (after a weekend) when I could have 3 days files generated and put into the folder on a Monday, so this code would only pick up the most recent. Which is why I thought a search for un-inputted files would work better.

Would appreciate your suggestions.

Many thanks

Jon

Hi Jon,
I do a similar thing where I add a daily workbook to a summary workbook sheet. Instead of trying to get the most recent, I always add what is selected by the user. Then I delete Duplicates from the data. If they add a file twice, by mistake, I simply delete the duplicate data.

I do a VBA code of Application.FindFile which brings up a dialog that they click on. I do some checks to insure it is the correct type of file, add to the bottom of my master and close it.
I use this code to open it:

Please Login or Register  to view this content.

After the VBA copies and pastes stuff to the clipboard I close this file using.

Please Login or Register  to view this content.

I have vba to delete dups at this point.

Hi

You could do something like the following.
Make a copy all the files you want to extract data from to a subfolder called Imports, which does not contain any other files.
then after you have completed all the work, delete all the files in that folder, so they cannot be imported a second time.

Then the frequency of running the code doesn't matter. If there is only 1 file in the folder, that's all that will be processed. If there are 10 there, then all 10 files will be processed.

Please Login or Register  to view this content.