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.
careybrophAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this codes


Sub importXl()
Dim xlObj As Object, fPath As String, shtName1 As String, shtName2 As String
fPath = "C:\MyFile.xlsx"
    Set xlObj = CreateObject("excel.application")
        xlObj.workbooks.Open fPath
        With xlObj
            shtName1 = .Worksheets(1).Name
            shtName2 = .Worksheets(2).Name
        End With
        xlObj.Quit
       
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, Tablex, "C:\MyFile.xlsx", True, shtName1 & "!"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, Tablex, "C:\MyFile.xlsx", True, shtName2 & "!"

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
careybrophAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.