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