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 ;)
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 ;)
Microsoft OfficeMicrosoft Excel
Last Comment
Tom Farrar
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
»bp
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?
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
https://trumpexcel.com/combine-data-from-multiple-workbooks/
https://www.youtube.com/watch?v=ldoQws7Zbx8