ObjWorkbook.Worksheets.Count not working

Question

I am trying to write a code to open an Excel file and then search through it. Even though, I am having problems just retrieving the number of worksheets in the workbook. I get the error:

424 - VBScript Runtime Error, Object Required

Any thoughts? Here is the small portion of the code I am having problems with:

Dim Shell : Set Shell = WScript.CreateObject( "WScript.Shell" )
Dim oFSO : Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim objExcel : Set objExcel = CreateObject("Excel.Application")

Dim myworkbook, worksheetcount
Set myworkbook = objExcel.Workbooks.Open("C:\Temp\Test.xlsx")

On Error Resume Next
Set worksheetcount = myworkbook.worksheets.count 'fails

If Err.number <> 0 Then
    ShowError()
    myworkbook.Close False
End if

Sub ShowError()
    WScript.Echo Err.Number & " Srce: " & Err.Source & " Desc: " & Err.Description
    Err.Clear
End Sub

Answer 1

The following would do the job.

VBA version

Dim Shell As Object: Set Shell = CreateObject("WScript.Shell")
Dim oFSO As Object: Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim objExcel As Application: Set objExcel = CreateObject("Excel.Application")

Dim myworkbook As Workbook, worksheetcount As Long
Set myworkbook = objExcel.Workbooks.Open("C:\Temp\Test.xlsx")

On Error Resume Next
worksheetcount = myworkbook.Worksheets.Count

If Err.Number <> 0 Then
    ShowError
    myworkbook.Close False
End If

VBScript version

Dim Shell: Set Shell = WScript.CreateObject("WScript.Shell")
Dim oFSO: Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim objExcel: Set objExcel = CreateObject("Excel.Application")

Dim myworkbook, worksheetcount
Set myworkbook = objExcel.Workbooks.Open("C:\Temp\Test.xlsx")

On Error Resume Next
worksheetcount = myworkbook.Worksheets.Count

If Err.Number <> 0 Then
    ShowError
    myworkbook.Close False
End If

You had error, because you tried to Set a value to worksheetcount which is not an object. You use Set only to objects.