VBA Macro to Create Power Query Connections for All Excel Tables

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

Watch on Youtube & Subscribe to our Channel

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.

Import Data Window Only Create Connection

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. 

Queries and Connections Pane showing all connections

How the Macro Works

When you run the macro it:

  1. 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.
  2. 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.
  3. Then the macro loops through all of the tables in all of the worksheets and creates connections for each table.
  4. 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.

Custom Macro Ribbon in Excel - Power Query Connect All Tables

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.

Modern Excel Blueprint Training Webinar Excel Campus Jon Acampora

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!

26 comments

Your email address will not be published. Required fields are marked *

  • Thanks for the workbook and nice & detailed explanation. It took 0.1 seconds on my machine.

  • This. Is. Awesome. Thanks for sharing this, Jon. I will get back with feedback once I’ve tried it out. Cheers! Peter

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • 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?

  • 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.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter