Data Import & Export

How to Query and Write Excel Tables Using SQL

Maira Mehtab Oct 10, 2026 869 views

Question details

The user wants to read data from one Excel workbook, process it using SQL queries, and write the output to a new table without requiring Microsoft Access on end-user machines.

How to Query and Write Excel Tables Using SQL

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data extraction and processing from external workbooks using standard SQL language.
Observed behavior: Looking for a reliable method to execute SQL statements on Excel tables and generate new resulting tables efficiently.

Before you start

Ensure that the source workbook you want to query is saved and accessible on your local drive or network, and note down the exact sheet names or table ranges before creating the connection.

Solution 1Recommended

Use Excel VBA and ADO for Direct SQL Processing

Execute SQL queries directly on Excel workbooks using ActiveX Data Objects (ADO) without needing Microsoft Access installed.

This is the most seamless method for end-users because it processes the SQL query entirely within Excel's background using the native ACE OLEDB provider.

Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) Editor.

Enable ADO Reference

Navigate to Tools > References in the top menu, scroll down, and check 'Microsoft ActiveX Data Objects Library' (choose the latest version).

Write the ADO Connection Script

Insert a new module and write a script using the ADODB.Connection object. Configure the connection string to point to your external Excel workbook using the 'Microsoft.ACE.OLEDB.12.0' provider.

Execute SQL and Write Data

Define your SQL statement (e.g., SELECT * FROM [Sheet1$]). Execute the query to generate a Recordset, and use the 'CopyFromRecordset' method to dump the processed data into your target worksheet.

Use Excel VBA and ADO for Direct SQL Processing

No Access Required: This method works perfectly for end-users who only have Excel installed, as the required database engine is typically included with Microsoft Office.

Solution 2

Use Microsoft Query for Custom SQL

Utilize Excel's legacy data tools to write custom SQL statements for importing data from other workbooks without writing VBA code.

Solution 3

Use Microsoft Access as an Intermediary Data Engine

If complex relational database operations are required, MS Access can link to the data, process it, and send the output back to Excel.

Advanced Data Processing Alternative

Process External Workbook Data in WPS Spreadsheets

WPS Spreadsheets fully supports VBA macros and external data connections, allowing you to run SQL queries via ADO to extract and process data from other workbooks efficiently.

1. Open the VBA Editor: Launch WPS Spreadsheets, navigate to the Developer tab, and click on 'Visual Basic Editor'.
2. Add ADO Library Reference: In the editor, go to Tools > References and enable the 'Microsoft ActiveX Data Objects Library'.
3. Write SQL Connection Code: Write your ADO connection script to query the external workbook using standard SQL syntax.
4. Execute the Data Import: Run the macro to execute the SQL query and automatically write the filtered data directly into your WPS spreadsheet.

Fully supports VBA and macros for advanced data processingHighly compatible with Microsoft Excel data formats (.xlsx, .xls, .csv)Seamlessly connect to external databases and workbooksLightweight architecture runs smoothly on older devices

microsoft office alternative - wps office

Frequently Asked Questions

How do I reference a specific Excel sheet in my SQL query?

When querying an Excel workbook using SQL, you must enclose the sheet name in square brackets and append a dollar sign at the end of the sheet name. For example: `SELECT * FROM [Sheet1$]`.

Do end-users need Microsoft Access installed to run Excel VBA SQL queries?

No, end-users do not need Microsoft Access installed. The Microsoft ACE OLEDB provider, which handles the SQL queries in the background for Excel files, is typically installed automatically alongside Microsoft Office.

Why do I get a 'table not found' error when querying an Excel workbook?

This usually occurs if the sheet name is misspelled, the trailing dollar sign ($) is missing in the SQL statement, or the connection string points to an incorrect file path.

Can I use SQL to update or write data back to the original Excel file?

Yes, using ADO you can run `UPDATE` or `INSERT INTO` SQL statements to modify an Excel workbook. However, Excel is not optimized to act as a concurrent relational database, so ensure the file is not opened by other users to avoid locking errors.

WPS Office