howto refer to the other EXCEL session

[Kanan]

I have VBA code creating another session of EXCEL and opening workbook
from my current workbook. here is my code:

Sub openwb_session(drillbook As String, gotosheet As String)
Dim xlapp As Excel.Application
Dim xlwkbk As Workbook
Set xlapp = New Excel.Application
Set xlwkbk = xlapp.Workbooks.Open(Filename:=drillbook)
xlwkbk.Worksheets(gotosheet).Select
xlapp.Visible = True

End Sub

My question is, how do I refer to the second session from my first
workbook's session later on. In other words I don't want this code run again
to opne another session of the workbook.

Please help

Kanan

 

[Tushar Mehta]

Make xlapp a module level variable along the lines of:

Option Explicit

Dim xlapp As Excel.Application

Sub openwb_session(drillbook As String, gotosheet As String)
Dim xlwkbk As Workbook
Set xlapp = New Excel.Application
Set xlwkbk = xlapp.Workbooks.Open(Filename:=drillbook)
xlwkbk.Worksheets(gotosheet).Select
xlapp.Visible = True

End Sub

Sub SomeOtherSub()
With xlapp
'...
End With

End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Kanan]

Thanks. It works fine. MY followup question is . How Do I check if there is
already session open for a particular workbook. I may be opening six
workbooks
in different session. The reason is the workbook names are same but they are
in different folders with different content.

Appreciate your help
Kanan