Does worksheet exist

[Fredrik Wahlgren]

Hi

Here'sthe background. I have a master workbook with a user defined function
that will fetch values from other open workbooks. The UDF will loop over
dynamically built referernce to particular cells and return a sum. This is
something I made for someone who needed to build a report based on the
values from these workbooks. The workbooks themselves have been generated by
some third party software. and contain financial information. This UDF works
very well and has saved lots of time by collecting this information
automatically. Previosly, the user had to do this error prone job manually
which took several hours

The problem is that there will be an error if the worksheet doesn't exist.
Fro some reason, it seems as if certain worksheets within the workbook won't
be generated.
I have already tested that the workbook is open.

How can I check if a worksheet exists in a different workbook from a UDF?? I
already know the name of the workbook where the worksheet is supposed to be.

Thanks in advance,
Fredrik

 

[Don Wiss]

How can I check if a worksheet exists in a different workbook from a UDF?? I
already know the name of the workbook where the worksheet is supposed to be.

I use the following, though it expects the workbook to be tested to be the
active one. Your macro can do this. Or modify the below. Don.

Function IsWorksheetOpen(worksheetname As String) As Boolean
' function tests for worksheet by that name exists

Dim shName As Worksheet

IsWorksheetOpen = False
For Each shName In Application.Worksheets
If shName.Name = worksheetname Then
IsWorksheetOpen = True
Exit Function
End If
Next shName

End Function

 

[Jim Cone]

Fredrik,

Something borrowed from Chip Pearson 04/20/2003
In public.excel.programming
'--------------
Function WorksheetExists(WSName As String, _
Optional WB As Excel.Workbook = Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, _
ThisWorkbook, WB).Worksheets(WSName).Name))
End Function
'-------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Fredrik Wahlgren" <[email protected]>
wrote in message
Hi
Here'sthe background. I have a master workbook with a user defined function
that will fetch values from other open workbooks. The UDF will loop over
dynamically built referernce to particular cells and return a sum. This is
something I made for someone who needed to build a report based on the
values from these workbooks. The workbooks themselves have been generated by
some third party software. and contain financial information. This UDF works
very well and has saved lots of time by collecting this information
automatically. Previosly, the user had to do this error prone job manually
which took several hours

The problem is that there will be an error if the worksheet doesn't exist.
Fro some reason, it seems as if certain worksheets within the workbook won't
be generated.
I have already tested that the workbook is open.
How can I check if a worksheet exists in a different workbook from a UDF?? I
already know the name of the workbook where the worksheet is supposed to be.
Thanks in advance,
Fredrik

 

[Fredrik Wahlgren]

Thank you Don and Jim

/Fredrik