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 = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 

Set dirObj = mergeObj.Getfolder("D:\Merge Files")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
kbay808Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
Have you double checked that all the files involved are .xlsx and not .xls?
.xls files can only have up to 65536 rows.
0
kbay808Author Commented:
All of the files that I want to merge are .xls files, but I’m merging them into a .xlsx file.
0
etech0Commented:
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"?
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

kbay808Author Commented:
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.
0
etech0Commented:
Aha!
Try changing this line
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Open in new window

to
Range("A2").End(xlDown).Offset(1, 0).PasteSpecial

Open in new window


If that fixes it I can explain it to you.
0
kbay808Author Commented:
I'm receiving the same error.
Run-time error ‘1004’: Application-defined or object-defined error”
0
etech0Commented:
Hmmm. Can you post example files that give the error?
0
broro183Commented:
hi,

Do you need to include at least one argument after the "pastespecial" statement?
(I'm guessing not, if the code has worked in the past.)

Here is a link to a free robust addin of Ron deBruin's that I have used repeatedly in different situations with no problems which may help. I have not tested it for >66k rows though.
http://www.rondebruin.nl/win/addins/rdbmerge.htm

hth
Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.