Merge multiple excel workbooks into 1
I’m using the below script to merge multiple excel workbooks into 1 using Excel 2010. It works great, but I need it to merge more than 65536 rows. I tried changing “A65536” to “A1000000” and “A1500000”, but I receive the following error. “Run-time error ‘1004’: Application-defined or object-defined error” How can I fix this scrip to increase to the maximum number of rows allowed?
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating
Set mergeObj = CreateObject("Scripting.Fi
Set dirObj = mergeObj.Getfolder("D:\Mer
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).
ThisWorkbook.Worksheets(1)
Range("A65536").End(xlUp).
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating
Set mergeObj = CreateObject("Scripting.Fi
Set dirObj = mergeObj.Getfolder("D:\Mer
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).
ThisWorkbook.Worksheets(1)
Range("A65536").End(xlUp).
Application.CutCopyMode = False
bookList.Close
Next
End Sub
ASKER
All of the files that I want to merge are .xls files, but I’m merging them into a .xlsx file.
If the source files are .xls, they can't have more than 65536 rows. What do you mean by "I need it to merge more than 65536 rows"?
ASKER
Sorry, I had a brain fart. I was thinking that the “A65536” was the maximum amount of rows in the output file. I should have noticed the amount of rows that were actually merged. When I run the scrip, I don’t have all of the data. I should have 169,176 rows, but only 117,351 rows were merged.
Aha!
Try changing this line
If that fixes it I can explain it to you.
Try changing this line
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecialRange("A2").End(xlDown).Offset(1, 0).PasteSpecialIf that fixes it I can explain it to you.
ASKER
I'm receiving the same error.
Run-time error ‘1004’: Application-defined or object-defined error”
Run-time error ‘1004’: Application-defined or object-defined error”
Hmmm. Can you post example files that give the error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
.xls files can only have up to 65536 rows.