To create a Power Query query for each Excel table, loop through the workbook’s worksheets and their ListObjects, add an M query that reads the table through Excel.CurrentWorkbook(), then create a Mashup OLE DB connection whose location matches the query name. The pattern below is designed for Excel desktop versions with the Get & Transform object model; it avoids duplicate-name errors on repeat runs by updating existing queries and connections.
How the macro maps tables to Power Query
An Excel table is a ListObject. Power Query can access it by name with Excel.CurrentWorkbook(){[Name="TableName"]}[Content]. Microsoft says Excel.CurrentWorkbook() returns tables, named ranges, and dynamic arrays—not worksheets (Microsoft: Excel.CurrentWorkbook).
As an Amazon Associate I earn from qualifying purchases.
The VBA object model keeps queries and connections in separate collections. Workbook.Queries contains the workbook’s Get & Transform queries, while Workbook.Connections contains workbook connections such as OLE DB connections (Microsoft: Workbook.Queries; Microsoft: Workbook.Connections). The query name and the connection’s Location must match.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsVBA macro: create or update a query and connection for each table
Paste this code into a standard VBA module in the workbook that contains the source tables. It creates one query and a corresponding Mashup connection per table. If a query or connection with the target name already exists, it updates the query or deletes and recreates the connection rather than failing on a duplicate name.
#1 Best Overall
Option Explicit
Public Sub CreatePowerQueryConnectionsForAllTables()
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim tableName As String
Dim queryName As String
Dim mFormula As String
Dim connectionName As String
Dim conn As WorkbookConnection
Set wb = ThisWorkbook
For Each ws In wb.Worksheets
For Each lo In ws.ListObjects
tableName = lo.Name
queryName = MakeQueryName(tableName)
connectionName = "Query - " & queryName
mFormula = BuildTableQuery(tableName)
If QueryExists(wb, queryName) Then
wb.Queries(queryName).Formula = mFormula
Else
wb.Queries.Add Name:=queryName, Formula:=mFormula
End If
' Recreate the connection so its Location matches the query name.
If ConnectionExists(wb, connectionName) Then
wb.Connections(connectionName).Delete
End If
Set conn = wb.Connections.Add2( _
Name:=connectionName, _
Description:="Power Query connection for table " & tableName, _
ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & queryName & ";Extended Properties=""""", _
CommandText:="SELECT * FROM [" & queryName & "]", _
lCmdtype:=xlCmdSql, _
CreateModelConnection:=False, _
ImportRelationships:=False)
Next lo
Next ws
End Sub
Private Function BuildTableQuery(ByVal tableName As String) As String
BuildTableQuery = "let" & vbCrLf & _
" Source = Excel.CurrentWorkbook(){[Name=""" & EscapeMText(tableName) & """]}[Content]" & vbCrLf & _
"in" & vbCrLf & _
" Source"
End Function
Private Function EscapeMText(ByVal value As String) As String
EscapeMText = Replace(value, """", """"")
End Function
Private Function MakeQueryName(ByVal tableName As String) As String
' Table names normally meet query-name needs. Replace unsupported punctuation
' and keep the original table name in the M source expression.
Dim result As String
Dim i As Long
Dim ch As String
result = ""
For i = 1 To Len(tableName)
ch = Mid$(tableName, i, 1)
If ch Like "[A-Za-z0-9_]" Then
result = result & ch
Else
result = result & "_"
End If
Next i
If Len(result) = 0 Then result = "TableQuery"
MakeQueryName = result
End Function
Private Function QueryExists(ByVal wb As Workbook, ByVal queryName As String) As Boolean
Dim q As WorkbookQuery
On Error Resume Next
Set q = wb.Queries(queryName)
QueryExists = Not q Is Nothing
On Error GoTo 0
End Function
Private Function ConnectionExists(ByVal wb As Workbook, ByVal connectionName As String) As Boolean
Dim c As WorkbookConnection
On Error Resume Next
Set c = wb.Connections(connectionName)
ConnectionExists = Not c Is Nothing
On Error GoTo 0
End FunctionThe connection string follows Microsoft’s documented Mashup connection shape: provider Microsoft.Mashup.OleDb.1, Data Source=$Workbook$, and Location set to the query name. The accompanying command selects from that same name (Microsoft Open Specifications: Retrieving Data From an SQL Source using Get & Transform). The object-model documentation and format example establish the relevant pieces, but do not promise identical behavior for every Excel build; verify this pattern in the desktop version and workbook format you use.
Important naming assumption
The helper converts punctuation in a table name to underscores to form a query name. If two distinct table names convert to the same query name—for example, names that differ only by punctuation—the macro can target the same query twice. Ensure table names produce unique query names, or change MakeQueryName to add a stable suffix. The M source continues to use the actual Excel table name.
Rank #2
What this macro does not configure
It creates a query and a workbook connection; it does not set up a worksheet output table or specify a load-to-worksheet destination. Do not assume the result is loaded onto a sheet just because the connection exists. Configure the desired load destination separately in Excel if you need visible output.
Recommended Free Tools
Run the macro safely
- Save a copy of the workbook as a macro-enabled Excel workbook if it is not already one.
- Confirm each source range is an Excel table and note its table name. In Excel, select a cell in the table and look for the table name on the Table Design tab.
- Open the VBA editor, insert a standard module, paste the code, and run
CreatePowerQueryConnectionsForAllTables. - In Excel, open Data > Queries & Connections and check that each table has a query. Confirm that each corresponding connection appears in the workbook’s connections list.
- Run the macro a second time and check that it completes without duplicate-name errors and that the existing queries still point to the intended tables.
Reruns, blank tables, and headers
Repeated runs
The macro updates an existing query’s formula and recreates the matching connection. It does not remove queries or connections for tables that have since been deleted or renamed, so those old entries may remain in the workbook and require manual cleanup. If other workbook features depend on a connection, deleting and recreating it may also affect those dependencies; review them before using this rerun behavior in a production workbook.
Blank tables and tables with headers
Test a blank table as well as populated tables before relying on downstream steps. The example’s M formula simply returns the table’s content; it does not add type conversions or repair missing or duplicate headers. Use meaningful, unique column names before building transformations that refer to those names.
If your macro also creates tables from ranges, specify whether the source range has headers instead of relying on Excel’s guess. A Microsoft Q&A answer about a reported case of headings becoming Column1, Column2, and so on recommends passing xlYes to the table-creation method when the range has headings. That is community troubleshooting guidance, not a guarantee about every Excel version (Microsoft Q&A: reported header-renaming issue).
Rank #4
Version and workbook-format considerations
Microsoft documents the Get & Transform query collection for Excel 2016 onward. Because the cited VBA references describe the collections but do not define a complete compatibility matrix for every desktop build, test the macro in the Excel installation where it will run.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Power Query connector limitations also depend on the host and file format. Microsoft’s Excel connector guidance notes that Power Query in Excel and Power Query Online cannot access encrypted Excel files; Power Query Online does not support password-protected Excel files; and legacy .xls or .xlsb files may require the Access Database Engine provider. These connector notes are not, by themselves, a complete statement of whether a particular VBA-generated desktop connection will work (Microsoft: Power Query Excel connector).
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
