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)
NameRequired/OptionalDescription
TransferTypeOptionalThe type of transfer you want to make. The default value is acImport.

NameValueDescription
acExport1The data is exported.
acImport0(Default) The data is imported.
acLink2The database is linked to the specified data source.
SpreadsheetTypeOptionalThe type of spreadsheet to import from, export to, or link to.

NameValueDescription
acSpreadsheetTypeExcel30Microsoft Excel 3.0 format
acSpreadsheetTypeExcel46Microsoft Excel 4.0 format
acSpreadsheetTypeExcel55Microsoft Excel 5.0 format
acSpreadsheetTypeExcel75Microsoft Excel 95 format
acSpreadsheetTypeExcel88Microsoft Excel 97 format
acSpreadsheetTypeExcel98Microsoft Excel 2000 format
acSpreadsheetTypeExcel129Microsoft Excel 2010 format
acSpreadsheetTypeExcel12Xml10Microsoft Excel 2010 XML format
TableNameOptionalA 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.
FileNameOptionalA string expression that’s the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNamesOptionalUse 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.
RangeOptionalA 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.
UseOAOptionalThis argument is not supported.

Example 1 – export Query to xlsx

Assume that you have the below Query

TransferSpreadSheet_02

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.

TransferSpreadSheet_03

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

TransferSpreadSheet_01

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

TransferSpreadSheet_04

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

TransferSpreadSheet_05

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

TransferSpreadSheet 001

In Access, you have a Query

Query1
Employee IDName
001Wyman
002Mary
003Peter

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.

TransferSpreadSheet 002

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

TransferSpreadSheet 003

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

6 thoughts on “Access VBA DoCmd.TransferSpreadSheet Method”

  1. Thank you, I had used this VBA command before but I just could not remember how to use it! Your clear notes explained exactly what I needed to know. Thanks again.

    Reply
  2. Simple and clear explanation! A question: can I set the data type of the fields using this DoCmd.TransferSpreadsheet acImport?
    i have columns with number but are imported as text instead of double…

    Reply
    • Hi Diego, I would suggest you to import the data into Access manually and you will find there are options for you to define data type of each field, then delete all the data. Afterwards, everytime you import data into Access, import into the same table.

      Reply

Leave a Comment