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

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.

Power Query Create Table Connections Macro.xlsm (92.7 KB)

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!

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

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

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

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >