Automation question

[gw.boswell]

Group,

I have a macro in MSWord that copies some text from a Word document
then opens an excel file and pastes the infomation into specified
cells. I am running Excel 2003 and Word 2003. Since I sometimes have
other workbooks open, I need to have the correct one visible and
active. Below is some code that does work. The problem is that it
assumes that the desired workbook is always instance 2 of excel.

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

Set objValBook = GetObject("C:\NMV\RUN\ValidationBS.xls")

objValBook.Application.Visible = True
objValBook.Parent.Windows(2).Visible = True

objValBook.Worksheets("Sheet1").Activate


I wanted to be able to cycle through the open workbooks and find the
correct one. So I opend an excel spreadsheet and wrote the following
code:

Dim num As Single, bIndex As Single
Dim xlApp As Object

Set xlApp = GetObject("C:\NMV\RUN\ValidationBS.xls").Application

num = 1

For Each W In Workbooks

If W.Name = "Validation.xls" Then
bIndex = num
xlApp.Parent.Windows(bIndex).Visible = True
End If
num = num + 1
Next

This workded fine. But when I put this code into the Word macro, it
failed. I assume that there is something required by Automation that I
am not doing but I don't know what. Any suggestions would much
appreciated.

Garry

 

[Steve Yandl]

Garry,

The subroutine below might do about what you want. When you open the VB
editor, go to 'Tools > References' and be sure to set a reference to the
Microsoft Excel Object Library for whatever version Office you have. Edit
the file name and file name including path to the xls file you're doing the
update on. For my test, I had an Excel file named "myBook.xls" saved in
"C:\Test" and I simply enter the text "Test 2" into cell C5 on Sheet1. The
sub checks to see if Excel is already running and then starts it if it
isn't. It then checks the collection of workbooks and grabs a reference to
the one to be edited if its already running, opens it if it isn't.

___________________________________

Sub UpdateAWorkbook()

Dim oXL As Excel.Application
Dim oOpenBook As Excel.Workbook
Dim oWB As Excel.Workbook
Dim XLwasNotRunning As Boolean
Dim WBwasNotOpen As Boolean
Dim wkbkToUpdate As String
Dim WBfilename As String

' Specify workbook to be updated
wkbkToUpdate = "C:\Test\myBook.xls"
WBfilename = "myBook.xls"

' If Excel already running, get handle, otherwise launch
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
XLwasNotRunning = True
Set oXL = New Excel.Application
End If
WBwasNotOpen = True

On Error GoTo Err_Handler

' Open the Workbook
For Each oOpenBook In oXL.Workbooks
If oOpenBook.Name = WBfilename Then
WBwasNotOpen = False
oOpenBook.Activate
Set oWB = oOpenBook
End If
Next oOpenBook

If WBwasNotOpen Then
Set oWB = oXL.Workbooks.Open(FileName:=wkbkToUpdate)
End If

oWB.Sheets(1).Cells(5, 3).Value = "Test 2"

If XLwasNotRunning Then
oWB.Close xlSaveChanges
oXL.Quit
End If

Set oWB = Nothing
Set oXL = Nothing
Exit Sub


Err_Handler:
MsgBox wkbkToUpdate & " caused a problem, Error: " & Err.Number
If XLwasNotRunning Then
oXL.Quit
End If


End Sub

__________________________________

Steve

 

[gw.boswell]

Alok, Steve.

Thanks you for your help. I combined the two suggestions and it seems
to work just fine. I have GOT to get the hang of this Automation thing
since I work with Excel and Word at the same time frequently.

Thanks again,

Garry