Access VBA DoCmd.TransferSpreadSheet Method

This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files.

You may also want to read:

Access VBA DoCmd.OutputTo Method

Access VBA auto generate mass report by group to Excel

Access Export all Queries to Excel

Syntax of DoCmd.TransferSpreadSheet Method

TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Name	Required/Optional	Description
TransferType	Optional	The type of transfer you want to make. The default value is acImport. Name Value Description acExport 1 The data is exported. acImport 0 (Default) The data is imported. acLink 2 The database is linked to the specified data source.
SpreadsheetType	Optional	The type of spreadsheet to import from, export to, or link to. Name Value Description acSpreadsheetTypeExcel3 0 Microsoft Excel 3.0 format acSpreadsheetTypeExcel4 6 Microsoft Excel 4.0 format acSpreadsheetTypeExcel5 5 Microsoft Excel 5.0 format acSpreadsheetTypeExcel7 5 Microsoft Excel 95 format acSpreadsheetTypeExcel8 8 Microsoft Excel 97 format acSpreadsheetTypeExcel9 8 Microsoft Excel 2000 format acSpreadsheetTypeExcel12 9 Microsoft Excel 2010 format acSpreadsheetTypeExcel12Xml 10 Microsoft Excel 2010 XML format
TableName	Optional	A string expression that is the name of the Microsoft Office Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Access select query whose results you want to export to a spreadsheet.
FileName	Optional	A string expression that’s the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames	Optional	Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.
Range	Optional	A string expression that’s a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
UseOA	Optional	This argument is not supported.

Example 1 – export Query to xlsx

Assume that you have the below Query

The below code exports the above Query as “test.xlsx”

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "staff_list_with_grouping", "C:\test\test.xlsx", True

Now you get the below file. Note that the headers squeeze together.

I usually write a VBA Procedure in Access to open the workbook automatically > auto fit > close it

Public Sub autoFormat()
    wkbookPath = "C:\test.xlsx"
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    With XL
        .Visible = False
        .displayalerts = False
        .Workbooks.Open wkbookPath
        .Columns("A:XFD").EntireColumn.AutoFit
        .ActiveWorkbook.Close (True)
        .Quit
    End With
    Set XL = Nothing
End Sub

Alternatively, you may also use DoCmd.OutputTo Method, the syntax is slightly different, and you will get well formatted headers same as that in Access Query. However, the file size is larger and it takes longer time to export.

DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, "C:\test\test.xlsx", False

Example 2 – import Excel (xlsx) to Access

The below code imports the data with headers of test.xlsx to Access table “importTable”. If the table does not exist, the table will be created. If the table already exists and contains data, the newly imported data will be added beginning from the last record. Creating a Table before import also ensure the data type of each field is what you expect; otherwise the data type will be automatically determined.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "importTable", "C:\test\test.xlsx", ", True

If you rerun the Macro, a new set of data will be imported again.

I have created another post specifically demonstrates how to import Excel to Access, including importing all workbook all worksheets to Access Table, click here to read.

Example 3 – export Query to Excel template

Assume that we have a formatted Excel template under C:\test\template.xlsx

In Access, you have a Query

Query1

Employee ID	Name
001	Wyman
002	Mary
003	Peter

In order to export Query 1 data to the template, Name the Excel Range B4:C10 as exportRange (you can define your own name). Your export Range can be larger than the actual export size. If you specify a Range name in the argument but it doesn’t exit in Excel, the argument name would become the Worksheet name.

Run the below Procedure in Access

Public Sub xportQuery()
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="Query1", FileName:="C:\test\template.xlsx", Range:="exportRange"
End Sub

Now the Query is inserted into the exportRange. Unfortunately the header must need to be included when export, there is no argument to control whether the header is exported.

As a workaround, we can write a Macro in Access to delete row4 in Excel.

Public Sub xportQuery()
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="Query1", FileName:="C:\test\template.xlsx", Range:="exportRange"

    Dim XL As Object
        Set XL = CreateObject("Excel.Application")
        With XL
        .Visible = False
          .displayalerts = False
          .Workbooks.Open "C:\test\template.xlsx"
          .Rows("4:4").Delete
          .ActiveWorkbook.Close (True)
          .Quit
        End With
        Set XL = Nothing
End Sub

Now after the Query is exported, the Excel is opened again > delete row 4 > and automatically close the file

If you are interested in learning how to run Excel Macro in Access, click here.

DoCmd.TransferSpreadsheet vs DoCmd.OutputTo

DoCmd.TransferSpreadsheet Method has other functions other than exporting, such as import files and exporting to an Excel template. In terms of exporting Query to XLSX, Transferspreadsheet is quicker and the file size is smaller because it does not contain any Query formatting, and error handling is different as well.

For exporting Query to Excel, I personally prefer OutputTo Method because I can adjust the column width in Query before export.

But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format. Some Access formats cannot be perfectly supported, and an error pops up when users try to open the exported workbook, and users have to save the file as a new one for auto repair. To work around this issue, you can export as XLS format instead.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLS, Outputfile:="C:\test\test.xls"

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff844793.aspx