VBA Code for MS Access 2013 to import Excel 2013 files and add additional data

[MicheleArenburg]

I am looking for a VBA code that will import an Excel spreadsheet, time stamp it, username stamp it, filename stamp it, (in separate fields).
Also, I need to prevent the same file from being able to be imported more than once.
Complete with Error Handling please.

I have tried the standard "DoCmd.TransferSpreadsheet" type code however, I need the details above handled as well.

My table tblPointsAwarded has these fields:
ID
OFFER
DESCRIPTION
Points
AccNum
Username (for stamp)
File Name (as imported by the user)
Data and Time (for Import time stamp)

Thank you , I appreciate any and all assistance with this.

 

[ranman256]

you can use Transferspreadsheet,
or
attach the spreadsheet as a linked table,
build a query to import the external table to your internal one,
add your needed fields to the query: userID, Filename, etc.
put these additional items on a form, frmImport, then the query can use the form items as fields to add.

 

[MicheleArenburg]

thanks for that,
I do have a need to eliminate the "human error" side of the data entry.. so I need to have the filename especially get picked up via coding to eliminate that. Also, I need to prevent the same file from being imported more than once.