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
Steve Yandl wrote:
> 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
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
|
Similar Threads
