How to show a message box that displays name for each worksheet in the excel file

[JonHuff]

I want this Access VBA to show a message box for each worksheet in the excel file. The MsgBox should say the worksheets name. HELP.

Sub ShowWorkSheets()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("L:\DOC-ADM\Business Analyst\Apr 08, FD SLA 2.xls")

  For Each xlSheet In xlBook
        MsgBox xlSheet.Name

    Next xlSheet

End Sub

[MMcCarthy]

Change For Each xlSheet In xlBook to

For Each xlSheet In xlBook.Worksheets

Mary

[hype261]

Try this code instead.

Sub ShowWorkSheets()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
  
  
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("L:\DOC-ADM\Business Analyst\Apr 08, FD SLA 2.xls")  

  For Each xlSheet In xlBook.Worksheets
        MsgBox xlSheet.Name
  
    Next xlSheet
  
End Sub

[MMcCarthy]

Snap :D

[ADezii]

Should you want all Worksheet Names to appear in a single Message Box:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strBuild As String
Const conPATH_TO_EXCEL_WORKSHEET As String = "L:\DOC-ADM\Business Analyst\Apr 08, FD SLA 2.xls"
  
  
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(conPATH_TO_EXCEL_WORKSHEET)
  
For Each xlSheet In xlBook.Worksheets
  strBuild = strBuild & xlSheet.Name & vbCrLf
Next xlSheet

strBuild = Left$(strBuild, Len(strBuild) - 2)

MsgBox strBuild, vbInformation, "Worksheets in " & conPATH_TO_EXCEL_WORKSHEET

xlApp.Quit