Accessing another instance of Excel in vba.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

grusenet@gmail.com - 14 Nov 2007 10:17 GMT

Hi

I have written a vba routine which automates Internet Explorer and
causes a web page to output a report in Excel. The web page creates a
new instance of Excel each time it produces the report.
My questions are, firstly, how do I get vba to 'see' the workbook in
the other instance of Excel so that I can save it and secondly, how
can I close the other instance of Excel after saving the file.

Reply to this Message

Joel - 14 Nov 2007 11:20 GMT

If you know the name of the workbook then use the workbook name

with workbooks("abc.xls") or similar)

If you don't know the name then try something like this

Sub test()

For Each wbk In Workbooks
If wbk.Name <> ThisWorkbook.Name Then
Set otherbook = wbk
End If
Next wbk
If Not IsEmpty(otherbook) Then
otherbook.Close
End If
End Sub

> Hi
>
[quoted text clipped - 4 lines]
> the other instance of Excel so that I can save it and secondly, how
> can I close the other instance of Excel after saving the file.

Reply to this Message

Tim Williams - 15 Nov 2007 05:11 GMT

> If you know the name of the workbook then use the workbook name
>
[quoted text clipped - 13 lines]
> End If
> End Sub

That would only work in the same instance of Excel: if another Excel is open
then it has its own separate Workbooks collection.

You could try using GetObject(), but there's no way to be certain of which
instance this would return. In my (brief) testing it seemed to return the
first-opened of two instances, so that's not going to help.

Tim

>> Hi
>>
[quoted text clipped - 4 lines]
>> the other instance of Excel so that I can save it and secondly, how
>> can I close the other instance of Excel after saving the file.

Reply to this Message