iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To create a Power Query query and workbook connection for every Excel table, loop through each worksheet’s ListObjects collection, add an M query that reads the table through Excel.CurrentWorkbook(), and create a Mashup OLE DB connection whose location matches the query name. The macro below is designed to skip matching queries and connections already present, so it can be run again without duplicate-name errors.
This approach uses the Excel workbook object model and Get & Transform features available in Excel 2016 and later. It is an implementation pattern, not a guarantee that every Excel build handles Mashup connections identically; test it in the desktop environment and file format you use.
What the macro creates
Each Excel table has a workbook name, such as SalesTable. The macro creates a Power Query formula equivalent to:
let
Source = Excel.CurrentWorkbook(){[Name="SalesTable"]}[Content]
in
SourceExcel.CurrentWorkbook() exposes tables, named ranges, and dynamic arrays—not worksheets. The table name in the formula must therefore match the actual table name, not the worksheet tab name. See Microsoft’s Excel.CurrentWorkbook reference.
#1 Best Overall
- Over 215 Microsoft Windows Excel Shortcuts
- Two-Sided Durable Laminiated Sheet
- Designed for Excel on a Windows Computer
The query and its connection are separate workbook objects. Workbook.Queries is the collection of Get & Transform queries, while Workbook.Connections holds workbook connections. Microsoft documents these collections in its references for Workbook.Queries and Workbook.Connections.
VBA macro: create a query and connection for each table
Paste this code into a standard VBA module in the workbook containing the tables. It uses each table’s name as the query name. Existing queries and connections with that name are left unchanged; the macro does not overwrite or repair existing objects.
Rank #2
- Instant Copilot. Unlock new possibilities with the dedicated Copilot key, which gives you instant access to experiences that can enhance your productivity¹.
- Enhance your experience With the new microphone mute key and snipping key
- Full keyboard experience. Features a full mechanical keyset, backlit keys, and a large trackpad for precise navigation and control. Optimal key spacing allows fast, fluid typing.
- Slim and compact Performs like a traditional, full-size keyboard.
- Clicks in place instantly Use in combination with the Surface Pro (11th Edition), Pro 9 and Pro 8* kickstand for a perfect laptop experience anywhere.
Option Explicit
Public Sub CreatePowerQueryConnectionsForAllTables()
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim tableName As String
Dim mFormula As String
Dim connectionString As String
Dim commandText As String
Set wb = ThisWorkbook
For Each ws In wb.Worksheets
For Each lo In ws.ListObjects
tableName = lo.Name
If Not QueryExists(wb, tableName) Then
mFormula = "let" & vbCrLf & _
" Source = Excel.CurrentWorkbook(){[Name=""" & _
tableName & """]}[Content]" & vbCrLf & _
"in" & vbCrLf & _
" Source"
wb.Queries.Add Name:=tableName, Formula:=mFormula
End If
If Not ConnectionExists(wb, tableName) Then
connectionString = _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _
"Data Source=$Workbook$;Location=" & tableName & ";" & _
"Extended Properties="""""
commandText = "SELECT * FROM [" & tableName & "]"
wb.Connections.Add2 _
Name:=tableName, _
Description:="Power Query connection for " & tableName, _
ConnectionString:=connectionString, _
CommandText:=commandText, _
lCmdtype:=xlCmdSql, _
CreateModelConnection:=False, _
ImportRelationships:=False
End If
Next lo
Next ws
End Sub
Private Function QueryExists(ByVal wb As Workbook, _
ByVal queryName As String) As Boolean
Dim query As Object
On Error Resume Next
Set query = wb.Queries(queryName)
On Error GoTo 0
QueryExists = Not query Is Nothing
End Function
Private Function ConnectionExists(ByVal wb As Workbook, _
ByVal connectionName As String) As Boolean
Dim connection As WorkbookConnection
On Error Resume Next
Set connection = wb.Connections(connectionName)
On Error GoTo 0
ConnectionExists = Not connection Is Nothing
End FunctionThe connection string follows Microsoft’s documented Mashup pattern: Provider=Microsoft.Mashup.OleDb.1, Data Source=$Workbook$, and a Location that identifies the query. The SQL command selects from the same name. Microsoft’s Get & Transform connection example documents this connection shape; it does not establish that every exact VBA call behaves identically on every Excel version.
How to run it
- Save a copy of the workbook as a macro-enabled
.xlsmfile. - Press
Alt+F11to open the Visual Basic Editor. - Choose Insert > Module, paste the code, and return to Excel.
- Run
CreatePowerQueryConnectionsForAllTablesfrom the Macro dialog or the Visual Basic Editor. - In Excel, check Data > Queries & Connections to confirm that the queries and connections appear.
How reruns and naming work
The macro checks both collections before adding an object. If a query exists but its connection does not, the run adds the missing connection. If a connection exists but the query does not, it adds the query. If both exist, neither is changed. This skip-existing behavior avoids duplicate-name errors, but it also means edits to a table or query are not propagated to an existing query formula.
Rank #3
- EXCEL SHORTCUTS. ZERO SEARCHING. – Our bestselling reference mat puts an extensive collection of commonly used commands, formulas and helpful tricks directly beneath your fingertips so you can find answers fast, work smarter and stay in the flow.
- YOUR DESK. SMARTER. – Clearly organized sections for navigation, selection, formatting, data and functions make it easy to find the right Excel command exactly when you need it.
- LEARN, WORK & RESET – Built-in desk-exercise diagrams give you 10 quick ways to stretch, recharge and return to work feeling sharper.
- ROOM TO WORK & CREATE – The extended 31.5 x 11.8-inch Pixiecube desk mat fits a laptop or keyboard and mouse, while the soft 2 mm surface adds comfort and protects your desktop.
- BUILT FOR REAL-WORLD WORKDAYS – A rugged stitched edge helps prevent fraying, and the water-resistant, stain-resistant surface protects against scratches, spills and everyday wear—because smarter desks should work harder.
Table names must be unique across the workbook for this name-based mapping to work cleanly. Excel table names normally satisfy workbook naming rules, but if you adapt the code to derive different query names, ensure those names are valid and unique and use the same derived name consistently in the M formula, connection name, connection location, and SQL command.
Connection creation is not the same as loading results
The code adds a workbook query and a Mashup connection. It does not explicitly load the query output to a worksheet table, and it does not set a connection-only or worksheet-load destination. Do not assume that creating these objects also gives you the output placement you want. If you need loaded results, configure the query’s load destination separately in the Excel interface or extend the VBA for the specific Excel version and load behavior you require.
Rank #4
- Efficient Media Controls: The Wired Keyboard 600, designed by Microsoft, features a Media Center with four hot keys for easy control of play/pause, volume up, volume down, and mute functions.
- Quiet and Responsive Keys: Enjoy a comfortable typing experience with quiet, thin-profile keys that are both responsive and efficient.
- Convenient Shortcuts: Quickly access common tasks with dedicated shortcut keys, including a calculator hot key and a Windows start screen key.
- Spill-Resistant Design: Work confidently with a spill-resistant design that protects your keyboard from accidental messes.
- Plug-and-Play Simplicity: No software needed—just connect the keyboard to your PC and start using it right away, with a full number pad for efficient data entry.
Header issues when tables are created from ranges
If your workflow first converts ranges into Excel tables, explicitly tell Excel whether the source range has headers instead of relying on its guess. For example, use xlYes when the first row contains headings:
Set lo = ws.ListObjects.Add( _
SourceType:=xlSrcRange, _
Source:=sourceRange, _
XlListObjectHasHeaders:=xlYes)A Microsoft Q&A response from volunteer moderator Andreas Killer in December 2020 suggested that relying on the default xlGuess can lead Excel to interpret headers differently when creating a ListObject. This is practical guidance for the reported column-renaming symptom, not an official compatibility guarantee. Before creating queries, make sure each source table has unique, meaningful column headings; downstream transformations may depend on those names. See the Microsoft Q&A discussion.
Best Value
- 💻 ✔️ EVERY ESSENTIAL SHORTCUT - With the SYNERLOGIC Reference Keyboard Shortcut Sticker, you have the most important shortcuts conveniently placed right in front of you. Easily learn new shortcuts and always be able to quickly lookup commands without the need to “Google” it.
- 💻✔️ Work FASTER and SMARTER - Quick tips at your fingertips! This tool makes it easy to learn how to use your computer much faster and makes your workflow increase exponentially. It’s perfect for any age or skill level, students or seniors, at home, or in the office.
- 💻 ✔️ New adhesive – stronger hold. It may leave a light residue when removed, but this wipes off easily with a soft cloth and warm, soapy water. Fewer air bubbles – for the smoothest finish, don’t peel off the entire backing at once. Instead, fold back a small section, line it up, and press gradually as you peel more. The “peel-and-stick-all-at-once” method only works for thin decals, not for stickers like ours.
- 💻 ✔️ Compatible and fits any brand laptop or desktop running Windows 10 or 11 Operating System.
- 💻 ✔️ Original Design and Production by Synerlogic Electronics, San Diego, CA, Boca Raton, FL and Bay City, MI, United States 2020. All rights reserved, any commercial reproduction without permission is punishable by all applicable laws.
Test the workbook before relying on the macro
Run the macro on a copy and verify these cases:
- Multiple tables: Confirm every table appears as a query and connection, including tables on different worksheets.
- Headers: Inspect the first row of each query to confirm headings were recognized as intended.
- Blank tables: Check how an empty table appears in the Power Query preview and whether it suits your downstream steps.
- Repeated runs: Run the macro again and confirm it skips existing objects without creating duplicate-name errors.
- Existing objects: If a query or connection already exists, inspect it manually; this macro deliberately does not update it.
Version and file-format limits
Microsoft’s Workbook.Queries documentation says Get & Transform support in this object model starts with Excel 2016. The collection property is read-only, but its collection exposes methods such as Queries.Add; read-only describes the property reference, not a prohibition on adding query objects.
Power Query behavior also depends on the host and workbook format. Microsoft’s Excel connector guidance says Power Query in Excel and Power Query Online cannot access encrypted Excel files, and Power Query Online does not support password-protected Excel files. It also notes that legacy .xls or .xlsb files may require the Access Database Engine provider. Those are connector-specific caveats, not a complete compatibility matrix for desktop VBA-generated connections. Test with the actual Excel desktop installation and file format you intend to use.
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.
Recommended Free Tools
