How to test if an Excel spreadsheet exists in VBScript?

[JP]

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

--
Regards,
Tom Ogilvy

"JP" <[email protected]> wrote in message
news:[email protected]...
> 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

[Dave Peterson]

Typo alert <vbg>:

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


Tom Ogilvy wrote:
>
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "JP" <[email protected]> wrote in message
> news:[email protected]...
> > 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

--

Dave Peterson

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Typo alert <vbg>:
>
> set sh = xlApp.WorkSheets("Test").Select
> should be:
> set sh = xlApp.WorkSheets("Test")
> (drop the .select)
>
>
> Tom Ogilvy wrote:
> >
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "JP" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
>
> --
>
> Dave Peterson

[JP]

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" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "JP" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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/de...stmOnError.asp

I can't reconcile your statement with your code.

--
Regards,
Tom Ogilvy



"JP" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "JP" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[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]


JP wrote:
> 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