Fix SELECT * FROM Query Name Error in Excel Power Query VBA
Question details
The user encounters an error when executing recorded VBA macros containing SQL-style commands like 'SELECT * FROM [Query Name]' for Power Query connections.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a recorded VBA macro to create or refresh a Power Query table.
- Observed behavior
- Excel rejects the SQL-style SELECT command because Power Query M queries are incompatible with recorded OLE DB SQL commands.
Ensure you have saved a backup copy of your Excel workbook containing the VBA code before modifying the macro to prevent any loss of complex Power Query configurations.
Refresh Existing Queries via ListObject or QueryTable
Instead of recreating the table with recorded OLE DB SQL commands, refresh the existing query directly.
Recorded macros often attempt to recreate the data connection using an OLE DB SQL string. Power Query utilizes M code, which frequently conflicts with standard SQL queries like 'SELECT * FROM [Query Name]'. Refreshing the existing object circumvents this conflict.
Press Alt + F11 in Excel to open the Visual Basic for Applications editor.
Find the module containing your recorded macro and locate the code block that includes the 'SELECT * FROM [Query Name]' command.
Delete or comment out the block of code attempting to recreate the query table.
Replace the deleted block with a VBA command to refresh the specific table, such as Sheets("Sheet1").ListObjects("Table1").QueryTable.Refresh BackgroundQuery:=False.
Alternatively, you can use the ThisWorkbook.RefreshAll command to update all data connections in the workbook simultaneously.
Consult Excel Developer Forums
For highly advanced automation needs that require dynamic query generation, reach out to specialized developer communities.
Switch to WPS Office for Seamless Spreadsheet Management
While advanced Power Query VBA troubleshooting can be complex in Microsoft Excel, WPS Spreadsheet offers a highly compatible, lightweight, and user-friendly alternative for managing your daily data tasks. It handles mainstream Excel files seamlessly and features an intuitive interface.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package.
- 2. Install the Application: Follow the on-screen instructions to install the lightweight WPS Office suite on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel workbooks to enjoy perfect compatibility and seamless data editing.
Frequently Asked Questions
Why does the macro recorder create an invalid SELECT statement for Power Query?
The Excel macro recorder translates data connection actions into legacy OLE DB SQL queries. Power Query uses a specialized M language, making the recorded SQL command incompatible with the existing Power Query structure.
Can I use SQL to query a Power Query connection in VBA?
Generally, no. Power Query relies on M language scripts. You should manipulate the query formula directly using VBA via the WorkbookQuery.Formula property rather than executing SQL SELECT statements.
How do I find the correct ListObject name to refresh via VBA?
Select any cell inside your Power Query output table, navigate to the 'Table Design' tab in the Excel ribbon, and locate the 'Table Name' box on the far left. Use this name in your VBA ListObjects("TableName") reference.
