Using Excel files from MS Access...

[GSMike]

I have been using Excel for reporting from MS Access from some time...my "MO" has been as follows:

Sub mike()
Dim xlApp as New Excel.Application, xlWkb as Excel.Workbook
set xlWkb=xlApp.Workbooks.Open(myPath & myFile)
etc.
etc.
End Sub

I need to know now, though, how to use an instance of Excel that is already running. I tried

Sub mike()
Dim xlApp as Excel.Application, xlWkb as Excel.Workbook
set xlWkb=Excel.Application.Workbooks("AlreadyOpen.xls")
Msgbox xlWkb.Name
End Sub

but I keep getting the "subscript of of range" error message.

Any suggestions?

Thanking you in advance,

Mike K

 

[GSMike]

OK, I had it, then I lost it. I did:

Sub mike()
dim xlApp as Excel.Application, xlWkb as Excel.Workbook
set xlApp=CreateObject("Excel.Application")
set xlWkb=xlApp.Workbooks("AlreadyOpen.xls")
msgbox wkWkb.Worksheets.Count
End Sub

It worked the first time I tried it, and then every time after that I got the "subscript out of range.." error message on the line that reads "set xlwkb=xlapp.Workbooks...".
Thanks for your help. Mike K

 

[GSMike]

OK, got it.
Use GetObject instead of CreateObject.
It works.
Answering my own questions here...just so no one else will waste their time!! Mike K