Importing data from many excel workbooks and sheets into a single workbook/table

I have 54 excel files with three sheets each, each sheet has a different amount of data entries but they are set out in a identical format, and 开发者_开发技巧I need to import the data from those sheets into a single workbook using VBA.

Is there any way I can program it so I can build the loops to import the data, but without having to write in each workbook name for each loop/sheet? I think I can use the call function, but I don't know how to make the loop codes independent of the workbook name they apply to.

Thank you so much in advance,

Millie

Sure just loop over the workbooks in a folder open them and then loop over their sheets. Depending on slight differences in format you might need to do some extra work when importing.

Sub ImportWorkbooks(destination as workbook, importFolderPath As String)

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(importFolderPath)

    'Loop through the Files collection and import each workbook
    For Each objFile In objFolder.Files
        Dim source As Workbook
        Set source = Application.Workbooks.Open(objFile.Path, ReadOnly:=True)
        ImportWorkbook source, destination
        wb.Close
        Set wb = Nothing
    Next

    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

Sub ImportWorkbook(source As Workbook, destination as Workbook)
    Dim sheet As Worksheet

    'Import each worksheet
    For Each sheet In source.Sheets
        ImportWorksheet sheet, destination
    Next sheet
End Sub

Sub ImportWorksheet(sheet As Worksheet, destination as Workbook)

    'Perform your import logic for each sheet here (i.e. Copy from sheet and paste into a 
    'sheet into the provided workbook)

End Sub

Basic usage would be something like the following to import into the current workbook:

ImportWorkbooks ThisWorkbook, "c:\path\to\folder\containing\workbooks\to\import"

It only takes two things: An array with the workbook file names in it, e.g.

 dim books
 books = array("book1.xls","book2.xls",....)

Then your loop code looks something like

dim myBk as Workbook
dim bkFile as string
For Each bkFile in books
   myBk = Workbooks.Open(bkFile, ReadOnly)
   myBk.Activate
   'Transfer cells from myBk to target workbook
   target.cells(--).Value = myBk.Sheets("myStuff").Cells(--) 
   ...
Next

I can't help you with the detail. You'll need to change the target.cells argument for each pass through the loop to shift the data destination.