Get Workbook path but from another excel instance

[georgesmailuk]

I open a rating workbook which, when a userform button is pressed,
opens another instance of excel and runs multiple workbooks that do
many calculations. When this is completed, i want the next part of the
rating code to remember the path for one of the two workbooks that are
left open in the other instance of excel.

When i try looking through the available workbooks, i get:
personal.xls
Book1.xls
rating.xls

These are the workbooks that are open in the 1st instance of excel.
How do i look at the workbooks in the 2nd instance.?
property listing.xls
summary.xls

I thought this code might do it, but just keeps looping through the
1st instance, without moving to the 2nd.
Code:
Do
Set XLAPP = GetObject(, "Excel.Application")
If Not XLAPP Is Nothing Then
For Each wkbk In XLAPP.Workbooks
If wkbk.Name = "Property Listing - " &
UserForm2.ComboBox1.Value & ".xls" Then
wkbk.Saved = True
x = wkbk.Path
wkbk.Close
End If
Next wkbk
End If
Loop


So what i want to do is:
open my rating workbook
press the userform button
have a 2nd instance of excel open
have multiple workbooks open to do calculations, leaving only 2
workbooks remaining

so far so good

have x = the path of the propertylisting workbook

x is in the code of the rating workbook userform and i can't get to
read the workbooks that are in the other instance of excel.

 

[paul.robinson]

Hi

Set XLAPP = GetObject(, "Excel.Application")

will return the existing object if one already exists.

You probably want
Set XLAPP = GetObject("", "Excel.Application")

which will return a second object (see the Help for GetObject).

Better still do this in your case:
Dim XLAPP as Excel.Application
Set XLAPP = New Excel.Application

regards
Paul

 

[georgesmailuk]

Thanks Paul.

Unfortunately it just gets stuck in a loop. I tried stepping through
it and the line
For Each wkbk In XLAPP.Workbooks
does not appear to find any workbooks.

Any other ideas?

George

 

[NickHK]

Using GetObject like that will return a reference to a (read any) instance
of Excel, possibly the one you are working in.

If you are creating a instance of Excel at the beginning, maintain that
reference and use it again later in your code:

Dim XLApp as excel.application
SEt xlapp=new excel.application

'Do you processing etc

dim XLWB as workbook
for each xlwb in alpp.workbooks

NickHK

 

[georgesmailuk]

Thanks Paul and Nick.

I was being a bit dim. I had the userform button running a macro that
opened the XLAPP and workbooks, but forgot to make XLAPP public so i
could use it in the userform code.

Looks like i can get it now.
Thanks for taking the time to respond.

George