Importing Multiple Excel files into Access with file path

[vbaddiction]

I have seen threads out there about how to import multiple files that are formatted the same into one table in Access. That is what I am trying to do but I also need to import the filename and path into a field in the table. Can someone post some code to do those two things together?

Much appreciated!

 

[Joe4]

It is the last argument of the TransferSpreadsheet action: http://msdn.microsoft.com/en-us/library/aa164087(v=office.10).aspx

Also note, if you create a Macro and use the TransferSpreadsheet action, it will list all the arguments and give you help on what you need to enter into each one. Once finished, you can convert that Macro to Visual Basic (one of the options in the Database Tools -> Macro menu/ribbon). I often use this method as a starting point for a lot of my code.

 

[vbaddiction]

Joe, I got it! Thanks for all your help. I now have a macro in Excel that names a range in all files in a directory. I also have a procedure in Access that imports the names ranges in all the files and adds the filename and path to each record.

Thank you