Add Filename to Imported Files from Excel VB

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,

Function Impo_allExcel()
Dim myfile
Dim mypath


mypath = "C:\My Documents\Test\"
ChDir (mypath)
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"
   
  End If
  myfile = Dir()
Loop
End Function
ctownsen80Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
after this line

DoCmd.TransferSpreadsheet acImport, 8, "Contacts_AVDC_NEW", mypath & myfile, True, "A7:H100"

'Add this line
currentdb.execute "update [Contacts_AVDC_NEW] set [FileNameField]='" & myfile & "' where [FileNameField] is null"
0
 
ctownsen80Author Commented:
Perfect! Thanks Rey!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.