Excel Worksheet and File Names in Table

[dancole42]

I've been through Ken's excellent list of Excel import VBA.

However, I'm not very good with VBA, and could use some help. I have a number of poorly formatted Excel files, with different layouts, etc. etc. etc., and I have everything I need to import it into Access and clean it up, but the one piece of the puzzle I'm missing is this: Ken's scripts allow importing into multiple tables where each table has the name of the origin worksheet, but does not give the ability to bring that worksheet name into the actual table.

What I need to be able to do is import every Excel sheet (with each sheet having anywhere from 1 to 10 columns), but rather than go into separate tables, I need everything to go into one table where, in addition to the data from the spreadsheet, each imported record also has a field showing the file name and the worksheet name.

Thanks for the help!!!

 

[Rx_]

Have a 3 day weekend here - leaving in 2 minutes. If you don't get help, let me know next tuesday. Will try to make some time to help you. I did something very much like this. You might search on Rx_ - can't remember what I posted or didn't post.

 

[JHB]

..... and I have everything I need to import it into Access and clean it up, ...

Could you show the code you have for importing?