Import or export data to/from a spreadsheet file.
Syntax DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA) Key TransferType The type of transfer to make. AcDataTransferType default = acImport. SpreadsheetType The type of spreadsheet to import from, export to, or link to. AcSpreadSheetType TableName The name of the Access table. For exporting, this can be the name of a SELECT query object. FileName The file name and path of the spreadsheet. HasFieldNames Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. default= False (0) treat the first row as normal data. When exporting, field names are always inserted into the first row of the spreadsheet. Range A valid range of cells or the name of a range in the spreadsheet. This argument is valid only for importing. default=entire spreadsheet. UseOA This argument is not supported..
With a linked Excel spreadsheet, you can view and edit the spreadsheet data with Access while still allowing access to the data from Excel.
When Access data is exported to a spreadsheet, the field names are always inserted into the first row.
In VBA: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "T_Employees", "F:\Employees.xls", True DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryEmployees", "F:\Employees.xlsx", True in PowerShell: $acExport = 1 $acSpreadsheetTypeExcel9 = 8 $db = New-Object -Comobject Access.Application $db.OpenCurrentDatabase("F:\Demo\Test.mdb") $db.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, "T_Sales", "C:\work\Sales.xls", $True) $db.Quit()
“Traditionally, most of Australia's imports come from overseas” ~ Keppel Enderbery
Importing Excel workbooks - MVPS Examples
TransferDatabase - Import or export data to/from another database.