Excel VBA: Check Whether Workbook Is Already Open Or Not

I often transfer data from current excel workbook to another excel workbook repeatedly. And I prefer creating vba macro to automate the task rather than doing it manually, which is very time consuming. So, before I open the destination excel workbook, I need to check whether the destination excel workbook is already open or not.

The following vba macro code is the function to check whether a workbook is already open or not. This function is quiet simple, it just do looping in Workbooks collection to check all opened workbook, if there is a workbook that have same name with the workbook we are looking for, then the function will return True or False if otherwise.

Function isWorkbookOpen(bookName As String) As Boolean
Dim vbResult As Boolean
Dim wbs As Workbook

vbResult = False
For Each wbs In Workbooks
    If UCase(wbs.Name) = UCase(bookName) Then
        vbResult = True
        Exit For
    End If
Next wbs
isWorkbookOpen = vbResult
End Function

This example show you how to use the function above.

Sub openWorkbook()
If isWorkbookOpen("macroexcel.xls") = False Then
   Workbooks.Open "d:\macroexcel.xls"
Else
   MsgBox "Workbook macroexcel.xls is already open."
End If
End Sub

Related posts:

• Excel VBA Examples: Opening & closing a workbook
• Excel VBA Tutorial: Looping
• Excel VBA Tutorial: Function Procedure
• Excel VBA Tutorial: Sub Procedure