Access 2007 VBA - Open Two Excel Workbooks

Question

I am using Access 2007. In my Access file I have VBA code that opens an Excel workbook when the Access program begins. The Excel workbook remains open while the program is running. I use this code to open the workbook:

Public xl As Object
Public wb1 As Object
Set xl = New Excel.Application
Set wb1 = xl.Workbooks.Open("c:\Book1.xlsx")

Now while the program is running I may need to open a second Excel workbook, get some data from it, then close it. I used this code:

Public wb2 As Object
Set wb2 = xl.Workbooks.Open("c:\Book2.xlsx")

But this doesn't work. It seems that the first workbook may have closed because if I do debug.print Workbooks.Count the response is "0".

Any suggestions on how to open a second workbook and still have the first workbook open? Should I create a second instance of Excel and use that to open the second workbook?

Thank you.

ashleedawg ashleedawg · Accepted Answer · 2018-07-21T03:57:28

You may have more luck with this procedure instead:

Sub OpenExcel(fName As String)
    Dim xlApp As Object
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        'Excel wasn't running, start it from code
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
xlApp.Workbooks.Open (fName)
End Sub

With your code it's possible that Excel was opening but the second workbook did not default to visible, which could be set with xl.Visible = True. Alternatively, a second xl object could have helped.

Regardless, this method is safer is a number of ways.

Make sure when you're done, make sure you clean up (and free up memory) with something like xlApp.Quit and Set xlApp = Nothing.

In fact, I'd suggest you reboot right now... before you do, take a look at the running processes in your Task Manager - I wouldn't be surprised if you have several "invisible" instances of Excel running. :)

Access 2007 VBA - Open Two Excel Workbooks

1 Answers