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:

    Application.FindFile 'Display Filefind Dialog
    strThatWorkbook = Windows.Application.ActiveWorkbook.Name 'Remember it's name

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

    Application.Workbooks(strThatWorkbook).Saved = True
    Application.CutCopyMode = False 'New code to stop Clipboard save message
    Application.Workbooks(strThatWorkbook).Close

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.

Dim foundfiles as string, mypath as string
dim x as long

 set mypath = ThisWorkbook.Path &"/Imports/"
    foundfiles = Dir(MyPath & "*.xl*")
    If foundfiles = "" Then
        MsgBox "No Excel files in folder to Import"
        Exit Sub
    End If
   
    x = 0
    Do While FilesInPath <> ""
        x = x + 1
        ReDim Preserve MyFiles(1 To x)
        MyFiles(x) = FilesInPath
        FilesInPath = Dir()
    Loop

'Do your stuff here to copy data in
MsgBox("All entries have been transfrerred into  this file",vbInformation)

    Kill mypath & "*.*"