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?
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.