Link to home
Start Free TrialLog in
Avatar of Rob
RobFlag for Australia

asked on

Script: How to merge many xlsx workbooks into one

How can I merge many xlsx workbooks into one?

Each workbook is more like a CSV in that there's only one sheet and every workbook has the same columns

Looking for any kind of solution that works eg PowerShell to VBS to odbc and so on

What I'm trying to do is just one one workbook with one sheet with all the contents on all the xlsx files. I'll keep working on it but just hoping someone out there helps me not reinvent the wheel ;)
ASKER CERTIFIED SOLUTION
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bill Prew
Bill Prew

How are the individual sheets to be merged going to be found, do you want to specify the names in the script, or process say all files in a folder, etc?


»bp
Avatar of Rob

ASKER

Good question Bill, there's just one sheet in each spreadsheet so it only needs to pick up sheets(1) i think (if using vba).  Process all files in a folder would work as well.. i'm getting them on email and saving to a folder.

Tom, I can see how power query could work here but i can't get it to work... i get "[Expression.Error] The import Parameter1 matches no exports. Did you miss a module reference?".  Any ideas with that?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
For Power Query to work with Excel, the data listings would need to be made into tables (Ctrl T), and kept in the same folder. If this will not work for you, then you should look at other options.  If it does work for you, the real advantage is that additional spreadsheets can be added to the folder, and will be updated in the consolidation process.