Excel 2010 Macro Check If Workbook Is Open
There are times when you need to check if a workbook is open. The function below returns True if the workbook is open and False if it is not:
BookOpen(Bk)
The argument is Bk, which is the name of the workbook being checked:
Function BookOpen(Bk As String) As Boolean
Dim T As Excel.Workbook
Err.Clear ‘clears any errors
On Error Resume Next ‘if the code runs into an error, it skips it and
‘continues
Set T = Application.Workbooks(Bk)
BookOpen = Not T Is Nothing
‘If the workbook is open, then T will hold the workbook object and therefore
‘will NOT be Nothing
Err.Clear
On Error GoTo 0
End Function
An example of using the function:
Sub OpenAWorkbook()
Dim IsOpen As Boolean
Dim BookName As String
BookName = “ProjectFilesChapter04.xlsm”
IsOpen = BookOpen(BookName) ‘calling our function – don’t forget the parameter
If IsOpen Then
MsgBox BookName &“ is already open!”
Else
Workbooks.Open (BookName)