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

 

[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