Home / Workbook / Open a Workbook

Open a Workbook

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.

About Excel Help Desk