VBA & Macro Problems

How to Use a Worksheet Variable in an Excel VBA SQL Query

John Wilson Oct 12, 2026 869 views

Question details

The user needs to dynamically query different Excel worksheets by passing a worksheet name variable into an ADO SQL query string.

How to Use a Worksheet Variable in an Excel VBA SQL Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing a VBA macro to perform ADO SQL queries dynamically across various worksheets without hardcoding the sheet names.
Observed behavior: The user wants to construct a valid SQL statement where the target table is represented by a dynamically assigned VBA worksheet variable, requiring proper formatting with brackets and a dollar sign.

Before you start

Ensure you have the Microsoft ActiveX Data Objects (ADO) Library enabled in your VBA editor references and that your target worksheets exist in the workbook to avoid runtime query errors.

Solution 1Recommended

Use String Concatenation for the SQL Statement

The most standard approach is to build the SQL string by concatenating the VBA variable directly into the FROM clause.

When using ADO to query Excel data, the worksheet acts as the database table. By isolating the worksheet name into a variable, you can dynamically update your query targets without rewriting the base code.

Declare the variable

Declare a string variable for your worksheet name (e.g., Dim sheetNameVariable As String).

Assign the worksheet name

Assign the desired worksheet name to your variable (e.g., sheetNameVariable = "DataSheet").

Concatenate the SQL string

Construct the SQL query by wrapping the variable with brackets and a trailing dollar sign. For example: sql = "SELECT * FROM [" & sheetNameVariable & "$]".

Execute the query

Execute your ADO recordset using the dynamically generated SQL string.

Use String Concatenation for the SQL Statement

Syntax Formatting: In Excel ADO queries, worksheet names acting as tables must be enclosed in square brackets [] and end with a dollar sign $.

Solution 2

Use the VBA Replace Function

Alternatively, you can define a template SQL string with a placeholder and use the Replace function to insert the variable.

WPS Spreadsheet Macro Support

Write and Execute VBA SQL Queries in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros and advanced data operations. You can easily set up variables to query different worksheets dynamically.

1. Open the VBA Editor in WPS: Launch WPS Spreadsheet, navigate to the 'Tools' tab on the ribbon, and click 'Macro' > 'Visual Basic Editor' to open the coding environment.
2. Enable the ADO Reference: In the VBA editor, click 'Tools' > 'References' and check 'Microsoft ActiveX Data Objects Library' to enable database queries.
3. Write the Dynamic Query Code: Declare your variable and concatenate it into the SQL string just like in Excel: sql = "SELECT * FROM [" & sheetNameVariable & "$]".
4. Run the Macro: Press F5 or click the 'Run' button on the toolbar to execute your code and query the worksheet data dynamically.

Full compatibility with Microsoft Excel formats (.xls, .xlsx, .xlsm) and macro scripts.Built-in VBA editor for writing, editing, and debugging your code seamlessly.Lightweight application that uses fewer system resources while processing heavy data queries.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need brackets and a dollar sign when querying an Excel worksheet?

In ADO SQL queries for Excel, the brackets [] define the worksheet as a table name to prevent errors if the name contains spaces or special characters. The dollar sign $ indicates that you are referencing the worksheet itself rather than a named range.

What happens if my worksheet name contains special characters?

If your worksheet name contains single quotes or other special SQL characters, you may need to escape them (usually by replacing a single quote with two single quotes) before appending the variable to your SQL query to prevent syntax errors.

Can I query a specific range within the worksheet dynamically?

Yes. To query a specific range instead of the entire worksheet, append the range address after the dollar sign inside the brackets. For example: sql = "SELECT * FROM [" & sheetNameVariable & "$A1:D100]".

Excel & Data Problems