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.

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


"(E-Mail Removed)" wrote:

> 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" <(E-Mail Removed)> wrote in message
news:942156B9-541C-4975-8148-(E-Mail Removed)...
> 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




> "(E-Mail Removed)" wrote:
>
>> 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.
>>
>>