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

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) into Access

The below code imports the data with headers of test.xlsx into 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.

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

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 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.

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.

Example 4 – import all Excel (xlsx) in the folder into Access

The below Procedure loops through all the xlsx files under c:\test\ folder and import data into Access table importTable

Public Sub import()
    Dim FileName, FilePathName, Path, FileNameList() As String
    Dim FileCount As Integer

    DoCmd.SetWarnings False
    Path = "C:\test\"
    FileName = Dir(Path & "")
    
    While FileName <> "" And Right(FileName, 4) = "xlsx"
        FileCount = FileCount + 1
        ReDim Preserve FileNameList(1 To FileCount)
        FileNameList(FileCount) = FileName
        FileName = Dir()
    Wend
    
    If FileCount > 0 Then
        For FileCount = 1 To UBound(FileNameList)
            FilePathName = Path & FileNameList(FileCount)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "importTable", FilePathName, True
        Next
    End If
    DoCmd.SetWarnings True
End Sub

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

4 thoughts on “Access VBA DoCmd.TransferSpreadSheet Method

  1. Tomasz says:

    Thanks, this is what I looked for, Thank You Sir! 😉

  2. Andy R says:

    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.

  3. Wyman W Wyman W says:

    Andy, it is very difficult even for me to remember this even though I have use numerous times!

  4. Damon says:

    You’re awesome! Been trying to figure this out for 3 days.

Leave a Reply

Your email address will not be published. Required fields are marked *