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!