How to control an already open workbook from Word   

Hi,

I would like to modify an already open workbook from Word VBA.

I found this macro:

Sub Macro1()

  Dim oXL As Excel.Application, oWB As Excel.Workbook

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

  Set oWB = oXL.Workbooks.Open("D:\workbook.xls")

  'my code

End Sub

It works fine, but if the file "workbook.xls" is already open, I have
an error.

Is it possible to modify this macro, so i can control the file
"workbook.xls" when it's already open?

Thanks!

date: Fri, 3 Oct 2008 07:06:30 -0700 (PDT)   author:   rog

RE: How to control an already open workbook from Word   

"rog" wrote:

> Hi,
> 
> I would like to modify an already open workbook from Word VBA.
> 
> I found this macro:
> 
> Sub Macro1()
> 
>   Dim oXL As Excel.Application, oWB As Excel.Workbook
> 
>   Set oXL = GetObject(, "Excel.Application")
> 
>   Set oWB = oXL.Workbooks.Open("D:\workbook.xls")
> 
>   'my code
> 
> End Sub
> 
> It works fine, but if the file "workbook.xls" is already open, I have
> an error.
> 
> Is it possible to modify this macro, so i can control the file
> "workbook.xls" when it's already open?

You have to iterate the Workbooks collection and see if your target workbook 
is listed. If it is, you set an object  to it, if not, you open it.

Something like:

Dim oXL As Excel.Application, oWB As Excel.Workbook
Dim i As Long
Set oXL = GetObject(, "Excel.Application")

For i = 1 to oXl.Workbooks.Count
   If oXl.Workbooks(i).Name = "workbook1.xls" Then
      Set oWB = oXl.Workbooks(1)
      Exit For
   End If
Next

If oWb Is Nothing Then
   Set oWB = oXL.Workbooks.Open("D:\workbook.xls")
End If

....

But you should check in an Excel newsgroup to make sure this si the best way 
of doing this.

Aslo, you need to check if Excel is running or not.
See the difference between GetObject and CreateObject in the VBA help.

date: Fri, 3 Oct 2008 12:00:00 -0700   author:   Jean-Guy Marcil

Re: How to control an already open workbook from Word   

On 3 Ott, 21:00, Jean-Guy Marcil
 wrote:
> You have to iterate the Workbooks collection and see if your target workbook
> is listed. If it is, you set an object  to it, if not, you open it.
>
> Something like:
>
> Dim oXL As Excel.Application, oWB As Excel.Workbook
> Dim i As Long
> Set oXL = GetObject(, "Excel.Application")
>
> For i = 1 to oXl.Workbooks.Count
>    If oXl.Workbooks(i).Name = "workbook1.xls" Then
>       Set oWB = oXl.Workbooks(1)
>       Exit For
>    End If
> Next
>
> If oWb Is Nothing Then
>    Set oWB = oXL.Workbooks.Open("D:\workbook.xls")
> End If
>
> ....
>
> But you should check in an Excel newsgroup to make sure this si the best way
> of doing this.
>
> Aslo, you need to check if Excel is running or not.
> See the difference between GetObject and CreateObject in the VBA help.
>

This code works great! Thanks for the help!

date: Fri, 3 Oct 2008 15:16:31 -0700 (PDT)   author:   rog