Automatically Create Data Connection for Tables

[gimmeexcel]

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?

 

[FranzV]

Have you tried using Excel.CurrentWorkbook() ?

It returns a table with all tables and defined names in your workbook. You can use it to filter the tables you want to merge and then expand, append or whatever you need.