MS-Access And Transfer SpreadSheet Command. ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

A very useful feature of Microsoft Access is the ability to transfer data between Access and Excel using the built-in Import/Export options. In this session, we will focus specifically on the Export process and examine the challenges that may arise after exporting data, particularly when using some of the various export options available in MS Access.

The simple VBA Command Syntax is:

Docmd.TransferSpreadsheet [Transfer Type],[SpreadSheet Type], _
[Input TableName/Query Name],[Output FilePath], _
True(HasFieldNames),Range,UseOA

The first parameter, [Transfer Type], can take one of two values: acImport or acExport.
The second parameter, Spreadsheet Type, accepts predefined options ranging from 0 to 10. These are part of an enumerated list that also includes support for Lotus worksheets.
The available options are:
- acSpreadsheetTypeExcel12Xml – 10
- acSpreadsheetTypeExcel12 – 9
- acSpreadsheetTypeExcel9 – 8
- acSpreadsheetTypeExcel8 – 8
- acSpreadsheetTypeExcel7 – 5
- acSpreadsheetTypeExcel5 – 5
- acSpreadsheetTypeExcel4 – 6
- acSpreadsheetTypeExcel3 – 0
- acSpreadsheetTypeLotusWJ2 – 4
- acSpreadsheetTypeLotusWk4 – 7
- acSpreadsheetTypeLotusWk3 – 3
- acSpreadsheetTypeLotusWk1 – 2
You can pass either the enumeration name or its corresponding numeric value as the second parameter.
The third parameter specifies the name of the input Table or Query to be exported (or imported).
The fourth parameter is the full path and file name of the output spreadsheet.
The fifth parameter is a Boolean value (True or False). Setting it to True ensures that the field names are included as the first row in the exported worksheet.
The optional Range parameter applies only when using acImport, allowing you to define the worksheet range from which data should be imported.
The final optional parameter, UseOA, is not defined and is typically not used.

Sample Transfer-Spreadsheet Command

Docmd.TransferSpreadSheet acExport,acSpreadSheetTypeExcel12xml, _
  ”Products”,”C:\My Documents\Book1.xlsx”,True

The options acSpreadsheetTypeExcel3 through acSpreadsheetTypeExcel9 create files in the Excel 97–2003 format with the .xls extension. These files can still be opened in Excel 2007, but if you explicitly give the output file a .xlsx extension, the file will not open in Excel 2007 or higher versions.
The option acSpreadsheetTypeExcel12 creates a file with the .xlsb extension. This is a binary-coded Excel file, fully compatible with Excel 2007 and above. It is especially useful for exporting large volumes of records, as it produces significantly smaller file sizes.
The option acSpreadsheetTypeExcel12Xml (note: often written as Excel12Xml) produces a file with the .xlsx extension, also compatible with Excel 2007 and higher.
When using acSpreadsheetTypeExcel9 or earlier, the exported file is functional but inherits the older Office 2003 theme, which can appear outdated in style compared to modern versions — as shown in the sample screenshot below.

Normally, after exporting, you may need to open the output file in your current version of Excel, update the formatting (such as font and font size), and then save it again in the latest Excel format. If you simply add the .xlsx extension to the target file name in the TransferSpreadsheet Command, expecting Excel 2007 or higher to recognize it automatically, the file will fail to open in those versions.

However, there is a simple trick to overcome this limitation. Using this method, the exported file will always be saved in the current version of Excel installed on your system — whether it is Excel 2007, 2010, 2013, or newer — regardless of which worksheet type you selected in the TransferSpreadsheet command.

A Simple Solution

First, create a blank Excel Workbook in your current version of Excel and save it in the desired target location.

Close the Workbook.

Run the `TransferSpreadsheet` command, using the saved Workbook’s file path as the target file parameter.

The exported data will be placed into a new worksheet within that Workbook. Since the Workbook was originally created in your current Excel version, the output will automatically adopt the default Office Theme of that version. This ensures that your exported data looks modern and properly formatted, as shown in the sample image below:

We have created three slightly different functions, each designed to save the output of the TransferSpreadsheet command in a distinct way.

The Export2ExcelA() Function.

The above Function creates a single WorkSheet as output in the Target WorkBook.

Public Function Export2ExcelA(ByVal xlFileLoc As String, ByVal QryORtableName As String) As String
On Error GoTo Export2ExcelA_Err
Dim tblName As String
Dim filePath As String
Dim xlsPath As String

Dim wrkBook As Excel.Workbook

'xlFileLoc = "D:\Blink\tmp2\"
'QryORtblName = "Products"

xlsPath = xlFileLoc & QryORtableName & ".xlsx"
If Len(Dir(xlsPath)) = 0 Then
    Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
End If
DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, QryORtableName, xlsPath, True

MsgBox "File: " & xlsPath & " Created ", , "Export2ExcelA()()"

Set wrkBook = Nothing
Export2ExcelA = xlsPath

Export2ExcelA_Exit:
Exit Function

Export2ExcelA_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelA()"
Export2ExcelA = ""
Resume Export2ExcelA_Exit

End Function

The Export2ExcelA() function requires two parameters:

The target path of the output Excel file.
The name of the input Table or Query.

In this example, the function exports data from the Products table, creating a worksheet in a workbook saved at the specified location.

At the start of the code, the function checks whether an Excel file with the specified name already exists.

If the file does not exist, a new workbook is created in the current version of Excel. The workbook is saved using the same name as the input table/query and then closed.
If the file already exists, the output worksheet is simply added to that workbook.

Now, what happens if we don’t pre-create a workbook in the current Excel version?

If no file extension is provided (e.g., C:\My Documents\Products) and the SpreadsheetTypeExcel9 option is selected, Access creates a new file with an .xls extension (Products.xls).
If the .xlsx extension is explicitly specified with SpreadsheetTypeExcel9, the command still creates the file, but it will not open in Excel 2007 or higher versions.
However, if an existing workbook (e.g., C:\My Documents\myBook.xlsx) is available, the exported data is added as a new worksheet. In this case, the sheet automatically inherits the current Excel version’s default Office theme.

This is why we explicitly create a new workbook in the current Excel version and save it to the target location in advance. Once saved, the workbook must be closed before referencing it in the TransferSpreadsheet command’s output file parameter.

⚠️ Important: If the target workbook is open when the command executes, an error will occur (“Source file not found”). The workbook must not be in use to avoid this issue.

Finally, the workbook pathname is passed to the TransferSpreadsheet command, and the export is completed successfully

Creating Separate WorkSheets in a Single Workbook.

Exporting Data into Separate Worksheets or Workbooks

Often, we need to export grouped subsets of data into Excel for reporting or distribution. For example:

Each region’s sales report is in a separate worksheet of a single workbook.
Each employee’s performance report is in a separate workbook.
Or, in our example, products are grouped by category into individual worksheets.

Using the Products table from the Northwind sample database, we’ll demonstrate how to export product data by category.

We can approach this requirement in two ways:

Separate Worksheets in a Single Workbook
- A single workbook (e.g., ProductsByCategory.xlsx).
- Each worksheet corresponds to a product category.
Separate Workbooks per Category
- Each product category is exported into its own Excel file.
- E.g., Beverages.xlsx, Condiments.xlsx, etc.

The Export2ExcelB() Function VBA Code:

Public Function Export2ExcelB(ByVal xlFileLoc As String, ByVal QryORtableName As String) As String
'----------------------------------------------------------------
'Creates separate Excel WorkBook for each Group of Records
'based on changing Query criteria.
'Uses Query Name Used for workBook Name
'----------------------------------------------------------------
On Error GoTo Export2ExcelB_Err
Dim strSQL As String
Dim m_min As Integer, m_max As Integer
Dim j As Integer
Dim qryName As String
Dim qryDef As QueryDef
Dim db As Database, rst As Recordset

Dim xlsPath As String
Dim xlsName As String
Dim wrkBook As Excel.Workbook

m_min = CInt(DMin("seq", "QryParam"))
m_max = CInt(DMax("seq", "QryParam"))

    xlsName = QryORtableName & ".xlsx"
    xlsPath = xlFileLoc & xlsName
    
If Len(Dir(xlsPath)) > 0 Then
    Kill xlsPath
End If

    Set wrkBook = Excel.Workbooks.Add
    wrkBook.SaveAs xlsPath
    wrkBook.Close
        
Set db = CurrentDb
For j = m_min To m_max

strSQL = "SELECT Products.[Product Code], QryParam.Category, " & _
"Mid([Product Name],19) AS ProductName, Products.[Standard Cost], " & _
"Products.[List Price], Products.[Quantity Per Unit] " & _
"FROM QryParam INNER JOIN Products ON QryParam.Category = Products.Category " & _
"WHERE (((QryParam.Seq)= " & j & "));"

qryName = "Category_" & Format(j, "000")
On Error Resume Next
Set qryDef = db.CreateQueryDef(qryName)
If Err Then
   Err.Clear
   Set qryDef = db.QueryDefs(qryName)
End If
On Error GoTo 0
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh
    
    DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, qryName, xlsPath, True
   
    db.QueryDefs.Delete qryName
Next
    MsgBox m_max & " Excel WorkSheets Created " & vbCr & "in Folder: " & xlsPath, , "Export2ExcelB()"
    Set wrkBook = Nothing
    Export2ExcelB = xlsPath
    
Export2ExcelB_Exit:
Exit Function

Export2ExcelB_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelB()"
Export2ExcelB = ""
Resume Export2ExcelB_Exit
End Function

Instead of creating a new workbook for each loop iteration,
You now create the workbook once before the loop starts,
Then inside the For...Next loop, you export each category’s products as a new worksheet into that same workbook.

That way, the end result is:

One Excel file (e.g., ProductsByCategory.xlsx).
Inside it, multiple worksheets, one per product category.
Each worksheet holds the data filtered by its category.

Here’s a refined explanation of that step:

Exporting Multiple Worksheets into a Single Workbook

In the revised code, we first create and save an empty Excel workbook at the target location. This ensures the workbook is in the current Excel version and theme.

Once the workbook is ready, we use a For...Next loop to go through each product category. For every category:

A query (or SQL statement) filters the products for that category.
The TransferSpreadsheet command exports the filtered dataset into the prepared workbook.
Each export creates a new worksheet inside the same workbook.

Because the workbook creation step is outside the loop, only one workbook is created, while multiple worksheets are added as the loop runs.

All Output Worksheets in Different Workbooks.

Loop through categories in the Products table.
Inside the loop, create a new workbook in the current Excel version.
Immediately close the workbook (to release file lock).
Call DoCmd.TransferSpreadsheet, passing the new workbook’s path.
The export command writes the category’s data as a single worksheet in that workbook.

This way:

Each category’s data lives in its own Excel file.
Useful when you need to distribute different files to different departments, customers, or teams.

The Export2ExcelC() Function VBA Code:

Public Function Export2ExcelC(ByVal xlFileLoc As String) As String
'----------------------------------------------------------------
'Creates separate Excel WorkBook for each Group of Records
'based on changing Query criteria.
'Uses Query Name Used for workBook Name
'----------------------------------------------------------------
On Error GoTo Export2ExcelC_Err
Dim strSQL As String
Dim m_min As Integer, m_max As Integer
Dim j As Integer
Dim qryName As String
Dim qryDef As QueryDef
Dim db As Database, rst As Recordset

Dim xlsPath As String
Dim xlsName As String
Dim wrkBook As Excel.Workbook

m_min = CInt(DMin("seq", "QryParam"))
m_max = CInt(DMax("seq", "QryParam"))

Set db = CurrentDb
For j = m_min To m_max

strSQL = "SELECT Products.[Product Code], QryParam.Category, " & _
"Mid([Product Name],19) AS ProductName, Products.[Standard Cost], " & _
"Products.[List Price], Products.[Quantity Per Unit] " & _
"FROM QryParam INNER JOIN Products ON QryParam.Category = Products.Category " & _
"WHERE (((QryParam.Seq)= " & j & "));"

qryName = "Category_" & Format(j, "000")
On Error Resume Next
Set qryDef = db.CreateQueryDef(qryName)
If Err Then
   Err.Clear
   Set qryDef = db.QueryDefs(qryName)
End If
On Error GoTo 0
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh

        xlsName = qryName & ".xlsx"
        xlsPath = xlFileLoc & xlsName
        Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
    
    DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, qryName, xlsPath, True
   
    db.QueryDefs.Delete qryName
Next
    MsgBox m_max & " Excel Files Created " & vbCr & "in Folder: " & xlFileLoc, , "CreateXLSheets()"
    Set wrkBook = Nothing
    Export2ExcelC = xlFileLoc & qryName & ".xlsx"

Export2ExcelC_Exit:
Exit Function

Export2ExcelC_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelC()"
Export2ExcelC = ""
Resume Export2ExcelC_Exit
End Function

A Demo Database with all three Function Codes, with sample Data of the Products table, and Queries is attached for Download.

Download TransferSpreadSheet1.zip