Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combine/merge multiple workbooks (2003)

    We have about 400 Excel files, all single-worksheet files, all formatted
    exactly the same (well, maybe some have 100 records and others have 200
    records, that part may change).

    What's the best way to pull the data from all 400 files into one
    worksheet. We know there won't be more than 65K rows.. in fact there
    will probably only be about 30K rows.

    I am not good with VB, so if you suggest code, please be specific.

    Thank you!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combine/merge multiple workbooks (2003)

    Place all workbooks in a single folder, and make sure that this folder doesn't contain any other workbooks.
    Copy the following macro into a module, then modify the constant strPath as needed.
    Save the workbook with the macro in another folder than the one containing the workbooks to be merged.
    Click anywhere in the macro, then press F5 to run it.
    <code>
    Sub MergeFiles()
    ' Path - modify as needed but keep trailing backslash
    Const strPath = "C:Excel"
    Dim strFile As String
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngMaxSourceRow As Long
    Dim lngMaxTargetRow As Long

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wshTarget = ActiveSheet
    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Set wbkSource = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
    Set wshSource = wbkSource.Worksheets(1)
    lngMaxSourceRow = wshSource.Range("A65536").End(xlUp).Row
    lngMaxTargetRow = wshTarget.Range("A65536").End(xlUp).Row
    wshSource.Range("1:" & lngMaxSourceRow).Copy _
    Destination:=wshTarget.Range("A" & (lngMaxTargetRow + 1))
    wbkSource.Close SaveChanges:=False
    strFile = Dir
    Loop

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    The worksheet that was active before you started the macro will be filled.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine/merge multiple workbooks (2003)

    Thanks, Hans. I did what you said to do. but nothing happens. The directory is "M:MelanieBehr_xls_File_Merge" and the file extensions are all .dbf. I am not sure if I inserted the module correctly - I really am dense when it comes to working with code. I modified the Const strPath = to "M:MelanieBehr_xls_File_Merge" and changed strFile = Dir (strPath & "*.xls") to .dbf. When I pressed F5, I got the go to dialog window. So, i tried running it from Tools, macros, but still nothing happened.

    Another angle of this issue would be that by my calculations, he has way too much data for one Excel file. Maybe we should go about this another way. Eventually, he wants to dump all of this into an Access database. I know how to import one table at a time into an Access db, but would it be better if we just went straight to importing all of this data into one table in Access. Then he can do whatever he wants with it there. He's pretty good in Access, but neither of us knows how to automate the import table process to get all 400 of these spreadsheets into one table in Access.

    Thank you for your help!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combine/merge multiple workbooks (2003)

    The path in the constant strPath should contain a backslash at the end:

    Const strPath = "M:MelanieBehr_xls_File_Merge"

    F5 to run a macro works only if you're in the Visual Basic Editor, not from Excel itself.

    It would be possible to import the DBase files directly into Access, but that would require code too. If you need that, you can post a new question in the Access forum.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine/merge multiple workbooks (2003)

    Ahh HHHHAAA!
    That worked! Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •