Sheet Exists in another file?

[Utkarsh]

Hi
I have files of the following kind in a single folder.

File 1 :
Workbook1 name = Anything
Sheet1 name = ABC
Sheet2 name = DEF
Sheet3 name = GHI etc

and several files each with the naming convention
Workbook2 name = ABC
Sheet1 name = ABC

Workbook3 name = DEF
Sheet1 name = DEF

etc. Basically I have a workbook each for each sheet in File 1. There
is a chance that some of the workbooks may have their name misspelt and
also the sheets may be misspelt.

I open the workbook corresponding to the sheet name in File 1 copy some
of its contents and close the other file.

I need to check if the workbook and sheet with the correct name exists.
I am able to track the presence of filename by:

Sub Macro1()
myfile = ActiveSheet.Name
mypath = ActiveWorkbook.Path
If Dir(mypath & "\" & myfile & ".xls") <> "" Then
MsgBox "File Found"
Else
MsgBox "Not Found!"
End If
End Sub

If the relevant file is found how can I detect if the sheet with the
same name also exists in that file i.e. if myfile & ".xls" is found
then is myfile & ".xls!" & myfile also found?

Thanks
Utkarsh

 

[Dave Peterson]

From Chip Pearson:

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

sub testme()
'lots of dim's, lots of code.
msgbox worksheetexists(myfile,workbooks(myfile & ".xls"))
end sub

This works if the workbook is open.