Activate excel workbook from Word

[Kengar624]

Assume that I have a specific Excel workbook that will ALWAYS be open. I simply want a VBA code that will activate the ALREADY-OPEN excel workbook from Word and then run an Excel macro.

I currently have one that will RE-OPEN the excel document and run the code but I only want it to ACTIVATE IT, not reopen.

Here's the code I have:

Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("P:\For Ken\OSTEEN POSTAL CALCS\99 - OSTEEN POSTAL CALC.xlsm")
oExcel.Visible = True
oWB.Application.Run "Module10.PASTE_FROM_WORD"

This will REOPEN the excel workbook even if it is already open...which, as stated, I DON'T want to do. Any help? Seems like it would be pretty straightforward but the stuff I've found on the 'net is ruh-diculous!

[Mavila]

Wouldn't it be:

oExcel.Workbooks("myWorkbookName").Activate

[Kengar624]

I get a Run-Time error...object variable with block variable not set

[Mavila]

OK, this works for me. Once you've opened the Workbook, try this:

Set myObject = CreateObject("Excel.Application") 'This is how I create the object in my macro
Dim wbName as String
wbName = myObject.ActiveWorkbook.Name
myObject.Workbooks(wbName).Activate

That works for me.

[gmaxey]

If you are sure that Excel is running with the workbook open then you don't need create a new instance of Excel:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oXLApp As Object
Dim oWB As Object
Set oXLApp = GetObject(, "Excel.Application")
Set oWB = oXLApp.Workbooks("Named Book.xlsx")
oWB.Activate
lbl_Exit:
Exit Sub
End Sub

[Kengar624]

Hey Greg! I get a subscript out of range error on line Set oWB = oXLApp.Workbooks("99 - OSTEEN POSTAL CALC.xlsm")

I double-checked the file name and even tried it with a new document named Book1 with the same results...

[gmaxey]

Ken,

I saved a workbook with that name an it worked fine so long as Excel is running and that named workbook is open. Try this and watch the immediate window as the open workbook names are printed.



Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oXLApp As Object
Dim oWB As Object
Set oXLApp = GetObject(, "Excel.Application")
For Each oWB In oXLApp.Workbooks
Debug.Print oWB.Name
Next
On Error Resume Next
Set oWB = oXLApp.Workbooks("99 - OSTEEN POSTAL CALC.xlsm")
If Err.Number = 0 Then
oWB.Activate
Else
MsgBox Err.Number & " " & Err.Description
End If
lbl_Exit:
Exit Sub
End Sub

[Kengar624]

OK it worked! I had to restart Excel...Thanks a lot guys!