Vba Macro To Create Power Query Connections For Any Table In Excel!

This video explains how to use a macro that I created to speed up the process of creating connections for all tables in a workbook. This is helpful when you are trying to merge or append tables in Power Query and don’t want to create connections one by one. This macro is free for you to copy and use.

connection-only-all-tables

Related Videos:
How to Combine Excel Tables or Worksheets with Power Query:
Power Query Overview - Automate Data Tasks in Excel & Power BI:
How to Install Power Query in Excel 2010 or 2013 for Windows:
How to Create a Personal Macro Workbook in Excel and Why You Need It:
How to Create Yes/No Message Boxes for VBA Macros:
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar:

Webinar: Free Excel Training on The Modern Power Tools

#MsExcel #ExcelCampus
00:00 Introduction
00:51 Run the Macro
02:20 VBA Editor

[lmulpagano]

So helpful! 7 connections, added to Data Model in 58.3 seconds. Over 500, 000 rows of data.

[bennicholls]

Super useful and well explained. Thanks Jon. 20 tables (some 40, 000+ rows) not added to data table 52 seconds.

[jorgeflores]

Hi Jon. Thank you for the tutorial. It was a great explanación on how Power Queries work.

[mtstans]

Jon thank you so much! You have saved me weeks lost from my life!

[raddleriddle]

Can you do a video of the reverse of this? How to loop through a bunch of PQs, adding each one to a new table in a new sheet ….. super helpful though. Thank you!

[faa]

hi, I don't see the file in your blog. Can you please link it here?

[hervemercier-glbp]

Hello Jon thank you so much for sharing your amazing skills.

[mivanows]

Thank you so much! It saves valuable time. I am getting an error when trying to add new connections:

"A query ti the name 'Append1' already exists."

- DEBUG:
'Add query

wb.Queries.Add Name:=sName, _
Formula:="let" & Chr(13) & "" & Chr(10) & " Source = & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"


Please advise!

[currentpeace]

Hello Jon, great video!!! Could you please help me with a code that deletes all queries in a Workbook. Thank you very much.

[wayneedmondson]

Hi Jon.. great video and clever solution for saving a lot of time loading tables into Power Query. Looking forward to studying the code further. Thanks for sharing. Thumbs up!!

[RolandK]

Thanks a lot for the example, - as I was not able to find anything in relation! :)
I was mostly interested in conncection.add2 option.
The code looks almost the same as I was working on.
In demo file I found some nice ideas which will need to test, however, the example/demo file actually does not work on my Excel 2016, - no error notification is produced...
"0 connections have been created in xx sec", - even if I remove all connections before running macros.
So I am quite pessimistic on my future tests :(
P.S. I want to create simple pivot table via VBA, with Data Model as I need to count unique values (distinct count) in the projects.... The main problem, - source filename always changes and I cannot produce the proper connection string.

[ahmedmedhat]

Thanks for your video, but I couldn't find the macro in the link.

[Gbv]

Hi Jon, great videos. Could you please organise a playlist for users at a beginner/intermediate level with all the videos about all the basic stuff and knowledge a beginner/intermediate should watch first?
Thank you, keep up the good work

[FrancescoGrosso]

Thanks!

What if you wanted to insert multiple connections to a workbook? Is that possible? E.g. Connect to two DBs and for sheet one use DB1 and for sheet 2 use DB2? I'm struggling to find that option

Thanks in advance :)

[Katnenis]

Hi Jon. I am trying to get some data from Azure DB using just a select statement. Can you help me on this please??

[anikmashudah]

Hi John. How to custom ribbon my macros like in your video?

[georginawu]

This is super useful especially when I have over 200 worksheets to create connections. However I couldn't find the Macro code on the other link, would you be able to send the Macro code?

[marcq]

Hi Jon,
Very instructive video, thank you.
I do have a question for a problem that I can't seem to find a solution. Maybe it is something simple for you and you might be able to help me.
I have to retrieve data from an ODBC connection into an Excel tool based on userform parameters.
The number of records can easily reach over 1M where the Excel sheet can't hold the recordset result. I am using ADODB recordset within my Excel VBA.
I thought that maybe I could create a power query in the workbook from the recordset directly.
For you information, if I create an ODBC via Data > Get Data and load to the query and data model, it takes over 3 x longer than my ADODB recordset to the ODBC DSN. Users might be ok to wait for 20 seconds, but definitely not 60 seconds each time they want to get data.
I appreciate your help if you can.
Thank you

[phongnguyenhuu]

Hi Jon. How to increase speed load data by power query in vba? Thank you very much

[JessicaDean]

I keep trying to download the workbooks to get the macro, unfortunately there are no modules in the file when I do so. Any suggestions?