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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.