Accessing another instance of Excel in vba.

[grusenet]

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.

 

[Joel]

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

 

[Tim Williams]

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

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