Excel Import Spec

[Guest]

I am trying to automate importing data from Excel. When I import an Excel
sheet I do not see the option of saving the import spec. The columns do not
have headings in Excel.
Am I missing something?

 

[John Nurick]

The Excel import wizard and the corresponding DoCmd.TransferSpreadsheet
method don't have import specs.

If you're importing to an existing table, the choices seem to be

1) Use TransferSpreadsheet with HasFieldNames:=0. That makes the Jet
ISAM use default field names F1, F2, F3... - so if you use the same
field names in your Access table the import will work smoothly.

2) Add field names to the Excel sheets.

3) Instead of TransferSpreadsheet, use an append query that maps the
default F1, F2... to your actual field names, e.g.

INSERT INTO MyTable
SELECT F1 AS FirstField, F2 AS SecondField, F3 AS NextField
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$]
;