Check a worksheet exists in a specified workbook
When opening a specified workbook, you may need to check that it contains a particular worksheet. First open the workbook – to do this, we’ll use the File Dialog picker. The code below opens the File Dialog, then opens the selected workbook.
___________________________________
Sub OpenFile()
Dim OpenFileName As Variant
OpenFileName = Application.GetOpenFilename() ‘Opens the File Dialog
If OpenFileName = False Then Exit Sub ‘If no file selected
Workbooks.Open (OpenFileName)
Call WorksheetCheck(OpenFileName) ‘Check if worksheet exists
ActiveWorkbook.Close False
End Sub
___________________________________
The sub WorksheetCheck(OpenFileName) checks to see if the specified worksheet – “Data” – exists in the selected workbook. For this we need a new sub and a function – specify the name of the worksheet you’re checking for in the new sub. The function simply returns a True/ False to the sub, then displays a message box.
___________________________________
Sub WorksheetCheck(OpenFileName)
If SheetExists(“Data”) = True Then
MsgBox (“The worksheet exists”)
Else
MsgBox (“The worksheet doesn’t exist”)
End If
End Sub
___________________________________
Function SheetExists(SheetName As String) As Boolean
‘ True if worksheet exists in selected workbook
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function
___________________________________
