Access VBA to Count Worksheets in a Workbook

Gary Croxford
Gary Croxford used Ask the Experts™
on
Thank you for looking at my question,

I need to count the number of worksheets in an Excel workbook.

the code I have started with is:
        If intFileSelectedFlag > 0 Then
            Dim objXL As Object
            Dim objWB As Object
            Dim objWS As Object
            
            Set objXL = CreateObject("Excel.Application")
            Set objWB = objXL.workbooks.Open(strSource, True, True)
            Set objWS = objXL.worksheet
            
            For Each objWS In objWB.worksheets
                lngSheetCount = lngSheetCount + 1
            Next
            
            MsgBox "No. Sheets: " & lngSheetCount, vbOKOnly, "Source: " & strSource
        End If

Open in new window


The code falls over at "Set objWS = objXL.worksheet" with run-time error: 438 Object doesn't support this property or method"

How do I do this please?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
HI,

pls try
        If intFileSelectedFlag > 0 Then
            Dim objXL As Object
            Dim objWB As Object
            Dim objWS As Object
            
            Set objXL = CreateObject("Excel.Application")
            Set objWB = objXL.workbooks.Open(strSource, True, True)
            
            MsgBox "No. Sheets: " & objWB.Worksheets.Count, vbOKOnly, "Source: " & strSource
        End If

Open in new window

Regards
Gary CroxfordOperations Support Analyst

Author

Commented:
Thank you
John SmithTepol

Commented:
It works for me as well, great job!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial