SQL to MS Excel – Export Exactly the Data You Need from Access

“Can you send me the active customers from City X, sorted by last name, as an Excel file?” – and next time it’s different. This self-contained VBA tool lets end users configure their own data exports: without SQL knowledge, without Access experience, without pulling the developer in every time.

A database user walks up to the administrator: “Can you send me the customer records from City X, active customers only, sorted by last name, as an Excel file?” No problem – but next week it’s a different filter, a different date range, different fields. The demand for targeted data exports is a near-daily reality.

That’s exactly what SQL to MS Excel was built to solve: a fully self-contained VBA tool that empowers end users to configure their own exports – without SQL knowledge, without Access experience and without pulling the developer in every time.

What does the tool do?

It consists of two components that can be used independently or together.

1. The interactive SQL Statement Builder (frmExportExcel)

A complete graphical interface for building SQL SELECT statements – the user doesn’t need to know SQL, the statement is generated automatically and in real time. The layout is deliberately linear:

1. Choose source table/query (dropdown, filled from a single constant)
2. Choose fields (double-click into the export list)
3. Define filters – up to two WHERE conditions with a full operator choice
4. Set sorting – any number of fields, one direction (ASC/DESC)
5. Limit the amount – optional TOP N
6. Check the SQL or export directly – the SQL can be edited manually first

2. The export function ExcelExportSQL (direct call)

If you already know the SQL or generate it in code, call the function directly – without the form:

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

Ideal for scheduled exports, reports from existing queries or cases where the SQL is already built in other modules.

The builder in detail

Live preview: every change (field selection, operator, sorting, TOP N) recalculates the SQL immediately and shows it in the txtSQL textbox. Useful side effect: the user can adjust the statement manually before exporting.

Filter operators – for each of the two filters:

Depending on the operator, the form shows or hides the matching input fields: BETWEEN shows from/to, IS NULL hides all fields, all others show a single value field.

Type-safe WHERE clauses

An often underestimated problem: the correct formatting of literal values. Depending on the field type, a value must appear differently in the SQL:

• Text: single quotes – 'Berlin'
• Date: Access hash format – #2024-12-31#
• Numeric: no quotes – 42
• Boolean: -1 (True) or 0 (False)

The tool determines the type via DAO: GetFieldType() opens a minimal SELECT TOP 1 snapshot and reads the Field.Type directly. FormatCriterion() then formats every value deterministically based on the actual field type.

The export function ExcelExportSQL in detail

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 – file name (without extension)
• sRange – start position of the data
• sHeader – title in cell A1
• sColumnsToDelete – comma-separated column names to delete

Flow in 19 steps (all documented in the source): validate SQL → open DAO recordset (dbOpenSnapshot) → catch empty result → prepare export parameters → connect Excel (GetObject/CreateObject) → create workbook → disable performance settings → transfer data via CopyFromRecordset → write column headers (incl. AS aliases) → delete unwanted columns (right→left) → find last data row → AutoFit → format header (+ AutoFilter) → freeze header → report title in A1 → format data range → page setup (A4 landscape) → restore performance → completion message (workbook stays open).

The sRange parameter (default "A2"): data from row 2, headers in row 1, title in A1. For multi-line title blocks e.g. "A4":

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

Multi-character column references ("AB10") are parsed correctly too.

Delete columns afterwards – internal fields (IDs, flags) that must stay in the SELECT (e.g. for JOINs) but shouldn’t be exported:

Call ExcelExportSQL( _
    "SELECT ID, FirstName, LastName, internalFlag FROM tblCustomers", _
    "CustomerList", _
    "A2", _
    "Customer List Export", _
    "ID,internalFlag")

Deletion happens right to left so the indexes stay stable.

Readable column headers via SQL AS alias:

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

Implementation – step by step

1. Import the form: import frmExportExcel into the target database.
2. Adjust EXPORT_SOURCES: the only mandatory configuration, at the top of the form module. Format "DisplayName;TechnicalName" – pairs separated by semicolons. Any number of sources, no further code needed.
3. Open the form – done.

Technical: late binding – no library reference

The entire Excel COM object is accessed via late binding; there is no reference to the Excel object library. All required Excel constants are declared as Private Const in 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 Excel version, without having to reset the reference after an Office upgrade.

Conclusion

SQL to MS Excel solves an everyday problem pragmatically and professionally: the end user gets an intuitive interface and professionally formatted Excel files, the developer gets a maintainable, well-documented module – with minimal configuration (a single constant) and no external dependencies.

Download