How to test if an Excel spreadsheet exists in VBScript?

[Guest]

I'm trying to reference an Excel spreadsheet that may not exist at the time
of the test, like this:
xlApp.WorkSheets("Test").Select
I would like to test if the "Test" sheet exist before selecting it. How to
do it in VBScript? I tried this:
IsObject(xlApp.WorkSheets("Test")), but it only worked if "Test" existed, it
gave me an error when it didn't.
Thanks a bunch,
JP

 

[Tom Ogilvy]

Dim sh as Excel.Worksheet
On Error Resume Next
set sh = xlApp.WorkSheets("Test").Select
On Error goto 0
if not sh is nothing then
sh.select
Else
msgbox "Test does not exist"
End if

 

[Jake Marx]

JP,

As Tom pointed out, you can use error handling to figure out if the sheet
exists or not. Here's a function that wraps it up for you:

Public Function gbIsValidWS(rwsName As String) As Boolean
On Error Resume Next
gbIsValidWS = Worksheets(rwsName).Index
End Function

However, I would suggest just trying to select the sheet - if it fails, you
can trap the resulting error and respond accordingly.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dave Peterson]

Typo alert <vbg>:

set sh = xlApp.WorkSheets("Test").Select
should be:
set sh = xlApp.WorkSheets("Test")
(drop the .select)

 

[Tom Ogilvy]

That's what you get for copying from the OP. <g>

 

[Guest]

The code you provided is not VBScript is it? The program I'm running only
understands VBScript. Your code gave me some good ideas, however it didn't
run in VBScript. Even the declaration "sh as Excel.Worksheet" gave me an
error. Besides the variable declaration this is what I'm trying to do:
Thanks

Set xlApp = Nothing
On Error Resume Next
'get an object reference to an open Excel app
Set xlApp = GetObject(,"Excel.Application")
On Error Goto 0
If xlApp Is Nothing Then
MsgBox "Fatal error"
End If

'This is the piece that you wrote, but it doesn't work
Set sh = Nothing
On Error Resume Next
set sh = xlApp.WorkSheets("Test") 'This line gives me an error
If sh Is Nothing Then
msgbox "Test does not exist"
Else
sh.select
End if
'Get the number of used rows from "Test" sheet
a = sh.UsedRange.Rows.Count
MsgBox(a)

 

[Tom Ogilvy]

You have the statements:

On Error Resume Next
set sh = xlApp.WorkSheets("Test") 'This line gives me an error

How are you getting an error if error handling says to trap the error and
continue?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsstmOnError.asp

I can't reconcile your statement with your code.