Import multiple worksheets

[wpryan]

Hi All, I have a service report in Excel format that is divided into 8 separate worksheets, and I would like to be able to import all of them at the same time into a database I'm developing. Until now I have been doing it manually in the File -> Get External Data -> Import, but this is a very laborious process that has an added risk of operator error. I've seen that it's possible to run a macro that would import worksheets, but in writing the macro Access needs to know the filename ahead of time. With my service report, all of the worksheets are named to correspond to the tables I want to import into, but the filename will not be consistent. Therefore I believe I'll have to do this in VBA. Although I have VBA experience in Excel, I don't know how to do it in Access. Would someone be able to point me in the right direction? Thanks ahead of time...

 

[HGMonaro]

Since you don't know the filename you're going to have to determine it using code. See this thread for a way to do that. Once you have that you can then do some docmd.transferspreadsheet cmds to import each sheet where ever you want it