Register
Log In
Unanswered
Home
February 23, 2017
Offline
Hi Forum Members,
Power Query is very useful in merging data but my big problem is when I have several tables to merge. Adding each one to a New Connection is a very slow process.
I'm wondering if anyone knows of a VBA solution that can identify every table (even on different Worksheets) within a workbook AND automatically create a data connection in Power Query?
July 16, 2010
Offline
Hi Digalo,
Yes, if you use From File > From Excel you can bring all of the sheets/tables/named ranges in, in one go. Make sure you check the 'Select Multiple Items' check box in the From File Navigator once you've chosen the workbook.
Mynda
February 23, 2017
Offline
Thanks Mynda. I appreciate your fast response.
So your solution works for a closed workbook, but does it work for an open workbook? If I currently have a Excel workbook open, I'm look for a way to add all tables/named ranges to a connection at once, instead of doing it one by one.
July 16, 2010
Offline
You can use the Excel.Workbook function in a blank query.
In the formula bar type:
=Excel.Workbook()
Press enter.
Most Users Ever Online: 57
Currently Online:
15 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
