instead of DoCmd.TransferSpreadsheet ,
Save new excel file the same file everytime. (make a macro to do it) like c:\temp\File2Import.xls
LINK this file as an attached linked table in access.
build your 'import' as an append query(ies) to import the data to the various tables
build a data verification (select) query (ies), to run check on fields that could be wrong.
select * where not IsDate([dateFld])
if it has records stop.
if Dcount("*","qsInValidDate")>0 then
docmd.openquery "qsInvalidDate"
end
endif
put in macro:
now the import process is just 2 steps:
1. Get a new excel file, save it to the c:\temp\File2Import.xls
2. run the import macro.
done
if no errors the final append query imports.
I actually had a table of the queries to inspect the import. if any had records, I stopped the import.