Bottom Line: Learn how to use a macro to create connection-only queries for all tables in the workbook. This includes adding the data to the Data Model.
Skill Level: Advanced
Video Tutorial
Download the Excel File with Macro
The macro I've created is included in this Excel file. This is the same workbook I use in the video. You can download the file and add the macro to your Personal Macro Workbook.
If you're new to Power Query, I recommend checking out this overview, and then using this tutorial to get it installed properly.
Speeding Up a Tedious Process
Creating connection-only queries to tables takes time. I've detailed the steps of the process in this post: How to Combine Tables with Power Query. It basically involves accessing the Import Data window and selecting Only Create Connection for each and every table that you want to combine.

However, I've automated this process with a macro that takes less than one second to run. The run time will vary depending on how many tables your workbook has, but it's pretty fast!
What the Macro Does
The macro creates connection-only queries in Power Query for all tables in the workbook.

How the Macro Works
When you run the macro it:
- First double-checks that you intend to run it with a yes/no message box. Checkout video #4 in my Personal Macro Workbook video series to learn more about yes/no message boxes.
- The macro then asks the user if the data should be added to the Data Model. This is the same as pressing the checkbox on the Data Import Window.
- Then the macro loops through all of the tables in all of the worksheets and creates connections for each table.
- The macro will only create connections if a query for the table does NOT already exist. It does not create duplicate queries for tables with existing connections.
Here is the VBA code for the macro:
Sub Add_Connection_All_Tables()
'Creates Connection Only Queries to all tables in the active workbook.
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As Boolean
Dim vbAnswer As VbMsgBoxResult
Dim vbDataModel As VbMsgBoxResult
Dim i As Long
Dim dStart As Double
Dim dTime As Double
'Display message box to prompt user to run the macro
vbAnswer = MsgBox("Do you want to run the macro to create connections for all Tables in this workbook?", vbYesNo, "Power Query Connect All Tables Macro")
If vbAnswer = vbYes Then
'Prompt user for Data Model option
vbDataModel = MsgBox("Do you want to add the data to the Data Model?", vbYesNo + vbDefaultButton2, "Power Query Connect All Tables Macro")
'Set variables
dStart = Timer
Set wb = ActiveWorkbook
'Loop sheets and tables
For Each ws In ActiveWorkbook.Worksheets
For Each lo In ws.ListObjects
sName = lo.Name
sFormula = "Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]"
'Check if query exists
bExists = False
For Each wq In wb.Queries
If InStr(1, wq.Formula, sFormula) > 0 Then
bExists = True
End If
Next wq
'Add query if it does not exist
If bExists = False Then
'Add query
wb.Queries.Add Name:=sName, _
Formula:="let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
'Add connection
wb.Connections.Add2 Name:="Query - " & sName, _
Description:="Connection to the '" & sName & "' query in the workbook.", _
ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Properties=""""", _
CommandText:="SELECT * FROM [" & sName & "]", _
lCmdtype:=2, _
CreateModelConnection:=False, _
ImportRelationships:=False
'Add to Data Model
If vbDataModel = vbYes Then
wb.Connections.Add2 Name:="Query - " & sName, _
Description:="Connection to the '" & sName & "' query in the workbook.", _
ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Properties=", _
CommandText:="" & sName & "", _
lCmdtype:=6, _
CreateModelConnection:=True, _
ImportRelationships:=False
End If
'Count connections
i = i + 1
End If
Next lo
Next ws
'Calc run time
dTime = Timer - dStart
MsgBox i & " connections have been created in " & Format(dTime, "0.0") & " seconds.", vbOKOnly, "Process Complete"
End If
End Sub
How to Use It in Your Work
This is a great macro to add to your Personal Macro Workbook because you will be able to run it on any open workbook. If you don't have your PMW set up yet, this tutorial will help you: How to Create a Personal Macro Workbook.
Then create a macro button by customizing the ribbon. Check out this article on How to Add Macro Buttons to the Ribbon for details.

You can then run the macro on any open workbook.
Free Training Webinar on the Power Tools
Right now I'm running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It's called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.

You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
The webinar is running at multiple days and times. Please click the link below to register and save your seat.
Click Here to Register for the Free Webinar
Conclusion
If you have multiple tables that you are looking to combine, this macro will save you tons of time establishing connections so that you can merge or append in Power Query.
If you use the macro, I'd love to hear how long it takes for the macro to create all of your connections. Leave a comment with the number of seconds it took!
Thanks for the workbook and nice & detailed explanation. It took 0.1 seconds on my machine.
Wow that’s a fast machine! Mine is a few years old, and now I’m jealous. Haha! 🙂
Thanks Saeed!
This. Is. Awesome. Thanks for sharing this, Jon. I will get back with feedback once I’ve tried it out. Cheers! Peter
Thank you Peter! I’m happy to hear you will be putting the macro to use. 🙂
Hi Jon,
many thanks for your fantastic work. Your tutorials are so great and i have learned so much!
I have an issue with the VBA-Macro which automatically builds the connections to tables.
Seems, that my Excel 2010 Version with installed Powerquery-Add-in has NO “WorkbookQuery”-Object, only “WorkbookConnection”-Object in VBA. I googled, that “WorkbookQuery”-Object is only available, starting Excel 2013/2016!
Please, could you confirm, that this may be the reason, why this very useful Macro can’t run to create the Powerquery-Connections?
Many thanks for your great work
Klaus
Hi Jon
Seems not to work in Office 2019?
Do I need to install Power Query there as well?
Thanks Jon for your work. I’m a big fan.
I want to propose a change in this ad connection macros. I verified and the macro is creating unnecessary connections when the user chooses to add to power pivot data model.
So if we have two tables it creates four connections, one in data model and another outside, when only two were necessary.
So I propose this change in the code:
Sub Add_Connection_All_Tables2()
‘Creates Connection Only Queries to all tables in the active workbook.
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As Boolean
Dim vbAnswer As VbMsgBoxResult
Dim vbDataModel As VbMsgBoxResult
Dim i As Long
Dim dStart As Double
Dim dTime As Double
‘Display message box to prompt user to run the macro
vbAnswer = MsgBox(“Do you want to run the macro to create connections for all Tables in this workbook?”, vbYesNo, “Power Query Connect All Tables Macro”)
If vbAnswer = vbYes Then
‘Prompt user for Data Model option
vbDataModel = MsgBox(“Do you want to add the data to the Data Model?”, vbYesNo + vbDefaultButton2, “Power Query Connect All Tables Macro”)
‘Set variables
dStart = Timer
Set wb = ActiveWorkbook
‘Loop sheets and tables
For Each ws In ActiveWorkbook.Worksheets
For Each lo In ws.ListObjects
sName = lo.Name
sFormula = “Excel.CurrentWorkbook(){[Name=””” & sName & “””]}[Content]”
‘Check if query exists
bExists = False
For Each wq In wb.Queries
If InStr(1, wq.Formula, sFormula) > 0 Then
bExists = True
End If
Next wq
‘Add query if it does not exist
If bExists = False Then
‘Add query
wb.Queries.Add Name:=sName, _
Formula:=”let” & Chr(13) & “” & Chr(10) & ” Source = Excel.CurrentWorkbook(){[Name=””” & sName & “””]}[Content]” & Chr(13) & “” & Chr(10) & “in” & Chr(13) & “” & Chr(10) & ” Source”
‘Add to Data Model
If vbDataModel = vbYes Then
wb.Connections.Add2 Name:=”Query – ” & sName, _
Description:=”Connection to the ‘” & sName & “‘ query in the workbook.”, _
ConnectionString:=”OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=” & sName & “;Extended Properties=”, _
CommandText:=”” & sName & “”, _
lCmdtype:=6, _
CreateModelConnection:=True, _
ImportRelationships:=False
‘Add connection only
Else
wb.Connections.Add2 Name:=”Query – ” & sName, _
Description:=”Connection to the ‘” & sName & “‘ query in the workbook.”, _
ConnectionString:=”OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=” & sName & “;Extended Properties=”””””, _
CommandText:=”SELECT * FROM [” & sName & “]”, _
lCmdtype:=2, _
CreateModelConnection:=False, _
ImportRelationships:=False
End If
‘Count connections
i = i + 1
End If
Next lo
Next ws
‘Calc run time
dTime = Timer – dStart
MsgBox i & ” connections have been created in ” & Format(dTime, “0.0”) & ” seconds.”, vbOKOnly, “Process Complete”
End If
End Sub
PLEASE SIR, CAN YOU MAKE A VIDEO TUTORIALS ON HOW TO CREATE A SPORTS/SOCCER MODEL. AM A SOCCER LOVING FUN. MY IDEA IS TO CREATE A SOCCER MODEL WITH COUNTRY DROP DOWN LIST SELECTION LIKE, ENGLAN, FRANCE LEAGUE 1, GERMANY BUNDASLIGA….AND DROP DOWN LIST OF INDIVIDUAL HOME AWAY TEAM TO GET MY PREDICTION.
it doesnt work for me. Does it matter my office is set in Spanish? Im not sure what Im doing wrong. 🙁
Can you help me to find the code to export data from excel to temple word ?. my mail: [email protected]
Thanks for this tutorial. It worked fine for me.
Now I’m trying to append queries with VBA, could you help me?
Hi Jon,
Thanks for sharing this powerful and useful VBA code.
I have a question here. If we want to create connections for several worksheets at one time, we can use “load to” function to create connection only to achieve that. Then when will we use this VBA code? Or is it because those tables we want to create connection are in the current active workbook? Just a little bit confused that.
Thanks for your help.
Do you have information on how I can access the power of power query through use of VBA to get folder where .csv files exist and append them as one file using Power Query? It would be awesome to also bring in whatever data cleansing I needed done in power query into the VBA as well.
The macro does not seem to work in Excel 2013 with Powerquery. WorkbookQuery is not defined. help?
For each table created, I would like to be able to add a custom column with the name “Join” and the custom column formula “=1”. Is this something that would be do-able with this code? Or after every table is created, would I have to go through and add these columns manually? The end goal is to use a power query to list all possible combinations of every table I have. I can do this manually, but it is a lot of data and I would prefer to automate the process.
How about create table relationship after loading queries.
Hi
I have tried this code in excel 2013 but got bug at wb.Queries.Add Name – line
Object doesn’t support this property or method.
Is query function not run in excel 2013 or something else?
Please help in resolving this error.
Great video but when I tried to run your sample code I get an error on
Dim wq As WorkbookQuery
I get Compile Error User defined-type not defined
Thanks
Me too, Please let me know if anyone have the solition
Please help! I’m having an odd issue where only one sheet is not showing up correctly on the Append sheet. All columns are the same however it’s set like there is another table to the right with the same titles (2). When I hover over the connection, the table is correct.
Oh my goodness, THANK YOU. This is literally EXACTLY what I was looking for. Thanks for the Macro. It took 132.1 seconds to make 110 connections.
Yeah…I didn’t want to do that manually. Because I have to do the same for 3 more workbooks now.
Thanks again, so much!
Wow!!! This tutorial is what I’ve been looking for, thanks to you sir.
By the way, I have a question, what If, I want to exclude one table from the rest of all the table. What code do I need to change or add?
Once again thank you very much.
Hello Jon,
I was on your webinar on Sunday, and I have to say that you are doing fantastic work!!!
I decided to join Elevate Excel and try to learn more about Power Query and VBA coding, as it does amazing things.
I have one question though. You have shared this unbelievable code for creating a Power Query connections so I wanted to check with you if I can use this macro for my work (I have combined it with the macro creating tables allowing your macro to make the connections 🙂 ) and share it with my colleagues, or if this is not allowed?
Thank you very much in advance for your reply.
Best wishes,
Boris
This macro is great! Thank you for sharing.
The issue that I’m having with it though, is that my workbook has several sheets with names that include a space. The table on those sheets is named with an underscore where the space would be since excel doesn’t allow spaces in table names. This seems to effect the macro since it can’t find the tables with the underscores in the name to create connections.
Is there a way to adjust for this?
Took 31.6 seconds for 57 connections. Saved me a TON of time.
I love this idea. I know enough to be dangerous and this helps solve some of my confusion. However, the download doesn’t have any macros in it that I can find. I’m on Office 365 if that makes a difference.