I have a VB code that imports data from multiple excel worksheets into a single Access 2010 table.
This code works just FINE for me.
But, I do need to add one thing. Along with the imported data it's capturing, I need the 'FileName' loaded into an additional column. Can someone tweak the code to account for this update?
The code is attached. Let me know if you have any questions for me to proceed,
mypath = "C:\My Documents\Test\"
myfile = Dir(mypath)
Do While myfile <> ""
If myfile Like "*.xlsm" Then
'this will import ALL the excel files
'(one at a time, but automatically) in this folder.
' Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "Contacts_AVDC_NEW", mypath & myfile, True, "A7:H100"
myfile = Dir()