Worksheet Exists?

[JC]

Hey all,

I have a tricky one here...

I'm running an access db that outputs data to excel in different tabs.
Before it outputs the data, it needs to check to see if a tab exists with a
certain name, if not it needs to create one with that name.

I'm running into a tricky problem where I can't seem to find a good way to
tell if the sheet exists or not.

The most common solution I've seen on the 'net involves using an "OnError
Goto 0" kind of solution. This only works if you have the setting for
Error-Breaking set to "Break on all unhandled errors". Unfortunately, the db
has to remain set at "Break on all errors", so it never hits that particular
code.

Any other ideas on how to find if the sheet exists?

Thanks in advance,
- JC

 

[Douglas J. Steele]

You can write a function that instantiates an instance of Excel and loops
through all of the sheets in it, comparing the names to the given name:

Function WorksheetExists( _
FullPathToWorkbook As String, _
WorksheetName As String _
) As Boolean

Dim objExcel As Object
Dim objWorksheet As Object

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open FullPathToWorkbook
For Each objWorksheet In objExcel.ActiveWorkbook.Worksheets
If objWorksheet.Name = WorksheetName Then
WorksheetExists = True
Exit For
End If
Next objWorksheet
objExcel.ActiveWorkbook.Close SaveChanges:=False
Set objExcel = Nothing

End Function