Foo - VBA assistance

Need a little VBA assistance if anyone knows how to do this:

I need to import data from tabs in Excel into an Access DB...that's easy enough.
The problem comes from the fact that each xls may have a different number of tabs I need to deal with, and it could be different from time to time. I want to be able to read the names of the tabs into an array or recordset and then I can spin back through the array/recordset to determine import eligibilty.
I can not find anything that discusses this type of situation but it should be doable.
Any help here would be greatly appreciated.

I use Apache-POI (http://poi.apache.org/)to read Excel spreadsheets in Java

Something like this:
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream("workbook.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
for( int i=0; i<wb.getNumberOfSheets(); i++ ){
HSSFSheet sht = wb.getSheetAt(i);

...
**
Sorry, it's not VB...best I could do :-(

I'm not sure if I'm understanding the question. If you want to go through the list of names of worksheets it's pretty straightforward. Something like:

Dim vntSheet As Variant
Dim colNames As New Collection
For Each vntSheet In ActiveWorkbook.Worksheets
colNames.Add vntSheet.Name
Next vntSheet

I'm not sure if I'm understanding the question. If you want to go through the list of names of worksheets it's pretty straightforward. Something like:

Dim vntSheet As Variant
Dim colNames As New Collection
For Each vntSheet In ActiveWorkbook.Worksheets
colNames.Add vntSheet.Name
Next vntSheet

That would work if you are writing the code in a Module in Excel.
This is VBA in the Access DB where I need a list of tabs to import.

I may use the code you provided to create a tab with a list of all the other tabs in the workbook though.
I could simply import that into a table in the database, read that into a recordset, and then have a list of all the tabs to import into the main table.

Thank you very much...now I just have to sell the company mgt. on that approach.

You can use that Excel code in Access. You need to reference the Excel library and dim a new Excel object.

Add the MS Excel Object Library

Dim xl as New Excel.Application
xl.Workbooks.Open (filename and options)

then do your stuff... you may need to fiddle with the Excel code offered above, but this is generally how you do it. I found it useful recording macros in Excel to see how its VBA works - best way to learn what you want to do in the Excel environment.

Depending on the format and reliability of your data, you can use this method to get the data directly while you have it open, rather than running the ImportSpreadsheet command, which has a lot of limitations especially if you want to avoid that smart-ass microsoft shtick of reformatting data types automatically.

I can't remember if it's visible by default - up to you how to use that. I found it fun to watch and useful for debugging, though it's slow if you're dealing with lots of data.

Don't forget to close the file and set the app to nothing before you exit the procedure. If you reply and nobody else can help and I don't get back here (I'm off and on these days), PM me.

-Nicodemus, your friendly BF Access guru

Have I ever said how much I love this place?
You guys rock!

Access - at least there's one thing I can do right :D