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?
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
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.
You can use the Excel.Workbook function in a blank query.
In the formula bar type:
=Excel.Workbook()
Press enter.
Thanks Mynda,
I couldn't get your suggestion to work, but Excel.CurrentWorkbook() did grab a list of tables/sheets.
This gets me to about 80% of what I wanted.
I appreciate your help!
Ah, yes sorry. I left out the 'current' part of the function 🙂 Glad you figured it out.
Mynda
