Automatically open, copy, and close multiple workbooks

[TJE]

I have 77 excel workbooks that are created each week, they are all in the same directory. I have to open each one and copy the data into a single workbook. The file names change slightly each week.

example: DIST_91124_GROWTH_PRODUCT XXX _07072006.xls The 07072006 is the week ending date and will change. Each of the 77 files has a different DIST_number.

Is there a way to automate this process, it takes me about 4 hours to do it manually.

[JMAN]

Hi,

Are all the files in the same directory? Are there any other files in that directory that aren't part of equation? What is it that you want to copy? Can you post a small workbook sample to work with?

JMAN

[TJE]

Yes they are all in the same directory and NO other files are in that directory

[JMAN]

What is that you want to copy? This would be a lot easier if you attached a sample of your data or were a little more descriptive.

[TJE]

What I have to do is open every one of the files in this directory, Select one of 3 tabs, copy that data into a new workbook. All the files have to be copied into the same workbook each below the previous copy. When done I have one worksheet with all the data from all workbooks. I have about 5000 rows of data when done. I use the data with macros I have written to produce several reports.

[JMAN]

TJE,

I can't help you with the code if I don't know what range you want copy, which sheet to copy from, what range to copy to, what sheet to copy to. All these small details matter, that's why I asked for a small workbook sample. See the sample code below and you can fill in the necessary data.

Sub RunCodeOnAllXLSFiles() 
    Dim lCount As Long 
    Dim wbResults As Workbook 
    Dim wbCodeBook As Workbook 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
     
    On Error Resume Next 
     
    Set wbCodeBook = ThisWorkbook 
     
    With Application.FileSearch 
        .NewSearch 
         'Change path to suit
        .LookIn = "C:\MyDocuments\TestResults" 
        .FileType = msoFileTypeExcelWorkbooks 
         '.Filename = "Book*.xls"
         
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0) 
                 
                 'DO YOUR CODE HERE
                 
                wbResults.Close SaveChanges:=True 
                 
            Next lCount 
        End If 
    End With 
     
    On Error Goto 0 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.EnableEvents = True 
End Sub 

[TJE]

Thankyou, I'll try to figure out how to use that code. I am also sending examples of my files.

WBExample.xls is the one to copy data from. All the data on the geography tab

CopytoWB.xls is the one to copy into

They are all in a directory c:\My Documents\Weeklies

Thanks again for your help

[TJE]

I uset the code you posted, inserted my data and it works perfectly. Will be a wonderful time saver. Thank you soooo much

[JMAN]

I uset the code you posted, inserted my data and it works perfectly. Will be a wonderful time saver. Thank you soooo much

Glad to help, but you can thank Dave Hawley..That sample came from his macros page

[TJE]

I have one little problem, when the workbooks are created, the first time they are opened a macro runs to populate the workbook. It doesn't run when I use your code and all I copy is a blank sheet. What code do I need to insert to ensure the macro runs on startup? The code works great on books I have already opened and saved.
Confusing I know, long story short I need code to force the auto macro to run before the copy and paste in my macro.