469,360 Members | 1,807 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. Sub ShowWorkSheets()
  2.     Dim xlApp As Excel.Application
  3.     Dim xlBook As Excel.Workbook
  4.     Dim xlSheet As Excel.Worksheet
  5.  
  6.  
  7. Set xlApp = New Excel.Application
  8. Set xlBook = xlApp.Workbooks.Open("L:\DOC-ADM\Business Analyst\Apr 08, FD SLA 2.xls")
  9.  
  10.   For Each xlSheet In xlBook
  11.         MsgBox xlSheet.Name
  12.  
  13.     Next xlSheet
  14.  
  15. End Sub
Oct 13 '10 #1
4 15026
MMcCarthy
14,534 Expert Mod 8TB
Change For Each xlSheet In xlBook to

Expand|Select|Wrap|Line Numbers
  1. For Each xlSheet In xlBook.Worksheets
Mary
Oct 13 '10 #2
hype261
207 100+
Try this code instead.

Expand|Select|Wrap|Line Numbers
  1. Sub ShowWorkSheets()
  2.     Dim xlApp As Excel.Application
  3.     Dim xlBook As Excel.Workbook
  4.     Dim xlSheet As Excel.Worksheet
  5.  
  6.  
  7.     Set xlApp = New Excel.Application
  8.     Set xlBook = xlApp.Workbooks.Open("L:\DOC-ADM\Business Analyst\Apr 08, FD SLA 2.xls")  
  9.  
  10.   For Each xlSheet In xlBook.Worksheets
  11.         MsgBox xlSheet.Name
  12.  
  13.     Next xlSheet
  14.  
  15. End Sub
Oct 13 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
Snap :D
Oct 13 '10 #4
ADezii
8,800 Expert 8TB
Should you want all Worksheet Names to appear in a single Message Box:
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Excel.Application
  2. Dim xlBook As Excel.Workbook
  3. Dim xlSheet As Excel.Worksheet
  4. Dim strBuild As String
  5. Const conPATH_TO_EXCEL_WORKSHEET As String = "L:\DOC-ADM\Business Analyst\Apr 08, FD SLA 2.xls"
  6.  
  7.  
  8. Set xlApp = New Excel.Application
  9. Set xlBook = xlApp.Workbooks.Open(conPATH_TO_EXCEL_WORKSHEET)
  10.  
  11. For Each xlSheet In xlBook.Worksheets
  12.   strBuild = strBuild & xlSheet.Name & vbCrLf
  13. Next xlSheet
  14.  
  15. strBuild = Left$(strBuild, Len(strBuild) - 2)
  16.  
  17. MsgBox strBuild, vbInformation, "Worksheets in " & conPATH_TO_EXCEL_WORKSHEET
  18.  
  19. xlApp.Quit
Oct 13 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Rick | last post: by
3 posts views Thread by Girish Sahani | last post: by
5 posts views Thread by soheir | last post: by
reply views Thread by John Sheppard | last post: by
2 posts views Thread by nse111 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.