Link to home
Create AccountLog in
Avatar of careybroph
careybrophFlag 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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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!