🚀 SQL to MS Excel - Export Exactly the Data You Need from Microsoft Access

A Problem Every Access Developer Knows

A database user walks up to the administrator and says: "Can you send me the customer records from City X, active customers only, sorted by last name, as an Excel file?" No problem. Next week it's a different filter, a different date range, different fields. The demand for targeted data exports from Access databases is a near-daily reality in any organisation running Access.

That is exactly the problem SQL to MS Excel was built to solve: a fully self-contained VBA tool for Microsoft Access that empowers end users to configure their own data exports — without SQL knowledge, without Access experience, and without pulling the developer in every single time.

What Does the Tool Do?

The tool consists of two components that can be used independently or together:

1. The Dialog Form – Interactive SQL Statement Builder

The form frmExportExcel is a complete graphical interface for building SQL SELECT statements. The user does not need to know any SQL — the statement is generated automatically and displayed in real time as selections are made.

The workflow is intentionally linear:

1. Select a source table or query – from a dropdown list populated by a single constant in the source code
2. Choose fields to export – double-click from the Available Fields list into the Selected Fields list
3. Define filters – up to two WHERE conditions with a full set of operators
4. Set a sort order – any number of sort fields, one shared direction (ASC/DESC)
5. Limit record count – optional TOP N to restrict the result to the first N rows
6. Review or export – the generated SQL can be manually edited before the export is triggered

2. The Export Function – Direct Call with Any SQL

If you already have an SQL statement — or build one in code — you can call the export function ExcelExportSQL directly, bypassing the form entirely:

Call ExcelExportSQL("SELECT * FROM tblCustomers", "CustomerList")

This is ideal for scheduled exports, reports based on existing queries, or any scenario where SQL is already being assembled in other modules.

The SQL Statement Builder in Detail

Live SQL Preview

Every change in the form — field selection, filter operator, sort entry, or TOP N value — immediately triggers a rebuild of the SQL statement. The result is always visible in the text box txtSQL at the bottom of the form.

This has an important practical benefit: the user can manually edit the generated statement before triggering the export. More experienced users or developers can fine-tune the SQL without resetting the entire selection.

Filter Operators

Both filter rows support the following operators:

Operator	Description
=, <>, <, >, <=, >=	Standard comparisons
BETWEEN	From/To query with two input fields
IS NULL / IS NOT NULL	No value / value present
= TRUE / = FALSE	Yes/No fields
LIKE *Wert*	Contains the term
LIKE Wert*	Starts with the term
LIKE *Wert	Ends with the term
NOT LIKE (3 variants)	Negation of the LIKE variants

 

Depending on the selected operator, the form automatically shows or hides the appropriate input fields: BETWEEN reveals two From/To boxes; IS NULL hides all input fields; all other operators show a single value field.

Type-Safe WHERE Clauses

One of the most commonly underestimated problems in dynamic SQL generation is the correct formatting of literal values. When a user enters a value into an Access text box and it gets embedded into a SQL statement, the result must match the actual data type of the field:

• Text fields need single quotes: 'Berlin'
• Date fields need the Access hash notation: #2024-12-31#
• Numeric fields need no quotes at all: 42
• Boolean fields expect -1 (True) or 0 (False)

The tool solves this with a DAO-based type lookup: the function GetFieldType() opens a minimal SELECT TOP 1 snapshot recordset on the source table and reads the Field.Type value directly from the DAO Field object. The function FormatCriterion() then formats every value deterministically based on the actual field type — regardless of what the value looks like as a string.

This prevents the well-known Access runtime error 3464 (Data type mismatch in criteria expression), which occurs precisely when a numerically formatted value is stored in a text field and gets embedded into SQL without quotes.

The Export Function ExcelExportSQL in Detail

Signature

Public Sub ExcelExportSQL(sSQL As String, _ 
                          Optional sFileName As String = "Export", _
                          Optional sRange As String = "A2", _
​                          Optional sHeader As String, _ 
                          Optional sColumnsToDelete As String)

sFileName  =  Filename (without extension) 
sRange  =   Top-left cell for data rows
sHeader  =  Titel text for cell A1 
sColumnsToDelete  =  Comma-separated column names to remove

 

Step-by-Step Execution

The function works through 19 clearly defined steps, all documented in the source code:

1. Validate SQL input – empty strings are rejected with a user message
2. Open DAO Recordset – dbOpenSnapshot for maximum read performance
3. Guard empty result sets – no empty, unsaved workbook is created
4. Prepare export parameters – parse the row number from the sRange parameter
5. Connect to Excel – reuse a running instance (GetObject) or create a new one (CreateObject)
6. Create workbook – a new blank workbook as the export target
7. Apply performance settings – disable ScreenUpdating, Calculation, EnableEvents (after Workbooks.Add!)
8. Transfer data via CopyFromRecordset – fastest method, no row-by-row loop
9. Write column headers – field names from the recordset, including SQL AS aliases
10. Delete unwanted columns – right-to-left, so indices remain stable
11. Detect last data row – via End(xlUp) from Excel
12. AutoFit – all columns and rows auto-sized
13. Format header row – bold, themed fill colour, borders, AutoFilter dropdowns
14. Freeze panes – header row stays visible while scrolling
15. Write report title – A1 with large, coloured Calibri font
16. Format data range – border grid, alignment
17. Page setup – A4 landscape, margins, print options
18. Restore performance settings
19. Completion message – workbook stays open; the user saves manually

 

The sRange Parameter

The default value is "A2". This means: data starts in row 2, column headers go into row 1, and the title always goes into A1.

If you want a multi-row title block or a blank row between the title and the headers, pass for example "A4": column headers land in row 3, data starts in row 4, and rows 1–2 are available for custom content.

Call ExcelExportSQL("SELECT * FROM tblOrders", "Orders", "A4", "Order Overview Q2 2024")

The parameter also handles multi-letter column prefixes like "AB10" correctly — the row number is extracted character by character, not simply with Mid(sRange, 2), which would break for two-letter column addresses.

Deleting Columns After Export

Sometimes a table contains internal fields (IDs, flags, technical keys) that must be present in the SELECT for JOIN or filtering purposes but should not appear in the final export. The optional sColumnsToDelete parameter accepts a comma-separated list of column header names:

Call ExcelExportSQL("SELECT ID, Vorname, Nachname, internerFlag FROM tblKunden", _
                    "Kundenliste", _
                    "A2", _
                    "Kundenliste Export", _
                    "ID,internerFlag“)

Deletion runs right-to-left so that removing one column does not shift the indices of the remaining candidates.

SQL Aliases for Human-Readable Column Headers

A very common real-world scenario: field names in the database are technically abbreviated (usr_nameFirst, cst_zipCode, ord_dtInvoice). The Excel output should show readable headings.

Solution: SQL AS aliases directly in the statement:

Call ExcelExportSQL("SELECT " & _
                    "usr_nameFirst  AS [First Name], " & _
                    "usr_nameLast   AS [Last Name], " & _
                    "cst_zipCode    AS [ZIP Code], " & _
                    "cst_city       AS [City], " & _
                    "ord_dtInvoice  AS [Invoice Date] " & _
                    "FROM tblOrders " & _           
                    "WHERE ord_active = True " & _
                    "ORDER BY usr_nameLast", _
                    "InvoiceExport")

The column headings in the Excel workbook will read exactly: First Name, Last Name, ZIP Code, City, Invoice Date — no post-processing required.

Implementation – Step by Step

Step 1: Import the Form

Import the form frmExportExcel as a .cls file into the target database:
Access → External Data → Access, or directly via drag-and-drop in the VBA Editor.

Step 2: Adjust EXPORT_SOURCES

The only mandatory configuration is at the very top of the form module:

Private Const EXPORT_SOURCES As String = _ 
              "Customer List;tblCustomers;" & _ 
              "Active Customers;qryCustomersActive;" & _ 
              "Orders 2024;qryOrders2024;" & _ 
              "Product Catalogue;tblProducts"

Step 3: Open the Form

​

Technical Details for Developers

Late Binding – No Library Reference Required

The entire Excel COM object model is accessed via Late Binding. There is no reference to the Excel object library (Microsoft Excel xx.x Object Library). All required Excel constant values are declared as Private Constants within the form module:

Private Const XL_CALCULATION_MANUAL  As Long = -4135
​Private Const XL_CONTINUOUS          As Long = 1
​Private Const XL_THIN                As Long = 2

Advantage: The tool works on any machine with any installed Excel version, without needing to update a library reference after an Office upgrade.

💬 Summary

SQL to MS Excel solves an everyday problem pragmatically and professionally. End users get an intuitive interface and professionally formatted Excel files. Developers get a maintainable, well-documented module that can be dropped into any Access database with minimal configuration — one constant to adjust, zero external dependencies, and a robust error handling architecture that makes production use safe and diagnosable.

🧪 Try it out

Using my little testing form you can try the functions directly. This makes it easy to learn the functions step by step. Do you develop a lot with Access? Then give it a try — I’m looking forward to your feedback or ideas for additional functions! 🚀

You will find the detailed description and all instructions directly in the code - well documented as usual.

 📥  DOWNLOAD  
Version: 1.0   4th May 2026