How to control an already open workbook from Word

R

rog

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!
 
J

Jean-Guy Marcil

rog said:
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?
Click to expand...

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.
 
R

rog

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.
Click to expand...

This code works great! Thanks for the help!
 
Top