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
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
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.