Avatar of careybroph
careybroph
Flag for United States of America asked on

VBA Get Excel tab name from within Access

So, I'm working with MS Access and I need to import two tabs (worksheets) from an Excel file. But when using DoCmd.TransferSpreadsheet acImport... I need to provide a range, including the worksheet. Unfortunately, they are not always named consistently (I can do nothing about this). BUT, it I will ALWAYS be importing data from the first two tabs. This leaves me with two possible options... that I'm not sure how to accomplish.

Code is something like this:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, Tablex, "C:\MyFile.xlsx", True, [worksheet name or number]

1. When importing data this way, is it possible to refer to a worksheet by it's number/index vs. the name? Not sure how I would reference the worksheet number/index?

Or...

2. Is there a way to get the names of spreadsheet tabs/worksheets from within MS Access prior to doing my import?

A little stumped on this one.
THANKS in advance for any insights. Let me know if I can clarify or better explain anything.
Microsoft AccessMicrosoft ExcelVisual Basic ClassicVBA

Avatar of undefined
Last Comment
careybroph

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
careybroph

ASKER
Sorry for the delayed response. Was out of the office and then buried on the return!  But your solution is excellent and much cleaner than my original approach. Thanks so much!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23