combine/merge multiple workbooks (2003)

Topic

New Reply

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!!

Reply | Quote

Replies

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.

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

The worksheet that was active before you started the macro will be filled.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

Ahh HHHHAAA!
That worked! Thank you.

Reply | Quote