Code for data import with calculated field

[Tyork]

I have an access table named SalesData with the following fields:

UID Autonumber


SaleDate
Test1
Test2
Test3
Test4

I want to import an excel sheet into the table but the table only has the Test1, Test2, Test3 and Test4 fields. The UID automatically generates upon import. The final field (SalesDate) I want to have is a date based on the name of the imported file. Ideally it would be the last 10 digits of the file name which would be a shortdate. Current code is:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TestImport", "C:\Documents and Settings\tyork\Desktop\TestImport.xlsx", True

which successfully imports the excel file without the saledate field to the SalesData table also without the SalesDate field. Any help would be greatly appreciated.

[NTC]

make a new table: ImportTemp that has all the fields you import plus the SalesDate field - but not the autonumber field.

import into a temp table ImportTemp - the SalesDate field will be empty (if I understand your post)

then apply an InsertQuery to ImportTemp table that populates your SalesDate field, all records - with the correct info.

then apply an AppendData query that puts all the ImportTemp records into the permanent table (which will trigger the autonumber values).

then apply a DeleteQuery to clear out all the records from ImportTemp table so it is clear for the next run (actually it is best to put this at the beginning of the process)

done this a million times - hope it helps.

[Tyork]

Perfect, thank you. I didn't consider breaking it up into multiple steps.