This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files.
You may also want to read:
Syntax of DoCmd.TransferSpreadSheet Method
TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
|TransferType||Optional||The type of transfer you want to make. The default value is acImport.
|SpreadsheetType||Optional||The type of spreadsheet to import from, export to, or link to.
|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) 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. 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.
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
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.
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"