Open a Workbook

0
91

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

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

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.