Access VBA equivalent of Import Spreadsheet Wizard

Question

Using the Access import spreadsheet wizard, I am able to set the Field Name, Data Type, Indexed and skip properties for each field input. Is there similar VBA code for doing the same import with specific field setting functionality?

The Access canned import spreadsheet wizard does what I want to do. It just appears that it can not be modified after the fact. Several fields default to date/time (dates in top of field rows) but it the data type needs to end up short text.

Access 2016 accdb and Excel 2016 xlsm file formats.

Lee Mac Lee Mac · Accepted Answer · 2019-04-09T22:03:34

The closest method to the functionality of the Import Spreadsheet wizard is the TransferSpreadsheet method of the DoCmd object.

However, whilst you can specify whether the first row of the spreadsheet contains field names, you cannot supply an import specification (as you might with the TransferText method), and so you cannot control the data types & indexing of each column, nor whether specific columns should be ignored by the import.

Possible alternatives might be to initially import the spreadsheet using the TransferSpreadsheet method, and then insert the imported data into a pre-existing table with the appropriate fields & indexing options configured as you require, e.g.:

insert into 
    myexistingtable ( field1, field2, field3 ... )
select 
    myimportedtable.column1, myimportedtable.column2, myimportedtable.column3 ...
from
    myimportedtable

Alternatively, you could bypass the TransferSpreadsheet method import and access the data held by the spreadsheet directly using ADO, e.g.:

insert into 
    myexistingtable ( field1, field2, field3 ... )
select 
    myimportedtable.column1, myimportedtable.column2, myimportedtable.column3 ...
from
    [Excel 12.0 Xml;HDR=Yes;Database=C:\YourExcelFile.xlsx].[YourSheet$A:Z]

Access VBA equivalent of Import Spreadsheet Wizard

1 Answers