The following routine will open a workbook and create a reference to it for use in further processing. This can be used in conjunction with the IsWorkbookOpen function to enure that we are not trying to open a workbook that is already open.
Open a Workbook
Step 1
Open a workbook, then start the Visual Basic Editor (Alt + F11) and copy the following macro’s into the editor
Sub OpenWorkbook(myWorkbook As Workbook, _
strFullFileName As String, _
strWorkbookName As String)
'This routine will attempt to open a workbook and then return a reference to the workbook
If Not IsWorkbookOpen(strWorkbookName) Then
Set myWorkbook = Application.Workbooks.Open(strFullFileName)
Else
Set myWorkbook = Workbooks(strWorkbookName)
End If
End Sub
Function IsWorkbookOpen(wbName As String) As Boolean
'Will verify if a Workbook is open before trying to open it
Dim wb As Workbook
IsWorkbookOpen = True
On Error Resume Next
Set wb = Workbooks(wbName)
If wb Is Nothing Then IsWorkbookOpen = False
End Function
strFullFileName As String, _
strWorkbookName As String)
'This routine will attempt to open a workbook and then return a reference to the workbook
If Not IsWorkbookOpen(strWorkbookName) Then
Set myWorkbook = Application.Workbooks.Open(strFullFileName)
Else
Set myWorkbook = Workbooks(strWorkbookName)
End If
End Sub
Function IsWorkbookOpen(wbName As String) As Boolean
'Will verify if a Workbook is open before trying to open it
Dim wb As Workbook
IsWorkbookOpen = True
On Error Resume Next
Set wb = Workbooks(wbName)
If wb Is Nothing Then IsWorkbookOpen = False
End Function
Step 2
To run this routine in your existing workbook code you could include something like the following
Sub TestOpenWorkbook()
Dim myWorkbookReference As Workbook
Dim strFileName As String
Call OpenWorkbook(myWorkbookReference, "c:\Temp\Test.xls", "Test.xls")
End Sub
Dim myWorkbookReference As Workbook
Dim strFileName As String
Call OpenWorkbook(myWorkbookReference, "c:\Temp\Test.xls", "Test.xls")
End Sub
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.
