VBA Code to Get the count of all sheets in an Excel Workbook
Sometime I come across with Excel reports with numerous worksheets / tabs.
Sometimes you can't find the starting or ending of the workbook.
Whenever I see those kind of Excel reports, I always wonder how many sheets will be there in that workbook !
Of Course, You can count the Excel sheets one by one. Select one sheet, then press CTRL + Tab to goto next sheet, keep on counting in your head. :) Very good time pass.
Excel is not providing a way to find how many worksheets are existing in a workbook.
If you want to know the count of all sheets in an Excel workbook, we have an easy way.
Steps:
Goto VBE, by pressing ALT + F11 or Click on VisualBasic Icon from the Develepor Tab.
Activate Immediate window by Pressing CTRL + G
?activeworkbook.Sheets.Count Copy paste the highlighted VBA code to immediate window and press Enter
You can see the count of the sheets in the active Excel workbook has printed just below the code which you have entered.
msgbox activeworkbook.Sheets.CountIf you use this code, a message box will be shown with count of sheets in the workbook.
This code becomes very important when you write VBA routines and loops.
When you play around with outlook macros to send mails, get mail data, etc, You will face an issue whether the end user has not opened the outlook.
If the outlook is not open when you run the code, you cannot get the desired results and your code will stop.
I made a simple code to check if the outlook is open while you run the code.
This can be used from Both Excel or Acces.
Sub CheckOutlookIsOpen()
Dim oOutlook As Object
On Error Resume Next
Set oOutlook = GetObject(, "Outlook.Application")
On Error GoTo 0
If oOutlook Is Nothing Then
MsgBox "Error : Outlook is not open! This cannot be run without opening outlook", vbCritical, "Error"
End
End If
End Sub
