Referencing Excel from a Word Macro when Excel and the file are already open?

Question

I'm sorry but after three days I Give Up... So I'm asking what should be a very simple question but every example I find opens an Excel file or opens a read Only version of the same file, to do something where as in my case the file has already been opened from another macro using the code below, I just can't seem to figure how to adapt it for a file already open??

I'm also trying to figure out how to move excel to the active window from within the code? I would really appreciate any help

Dim oApp As Object
Dim x As Variant
Dim sPath As String
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim oSheet As String

sPath = "E:\Special Folders\WWWRoot\temp.xlsx"
oSheet = "--Keywording--"

On Error Resume Next


    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open(sPath)
    oExcel.Visible = True
    Sheets(oSheet).Select

    Range("A1:G1000").Clear
    Range("A1").Select

    Sheets(oSheet).Cells(1, 1).Select
    Sheets(oSheet).PasteSpecial (xlPasteAll)

    Range("A1").Select

something like this set oExcel = GetObject(, "Excel.Application") or Set oExcel = GetObject("Book2").Application Read this for more details:support.microsoft.com/en-us/help/288902/… — cyboashu
The code you have will do what you want. At the end of that code, your Excel workbook will be active. Why do you think it isn't working. Or does the rest of your macro, or another macro, switch the active application back to your Word app, and you are trying to get it back to the Excel app again? (In which case you probably just need to pass your reference to oWB between your macros.) — YowE3K
Thanks for the Help: I was able to do it with the following but I cant seem to referance the Sheet using the GetObject(oSheet).Application which the MSFT Document said would work. Thanks again - CES Dim oSheet As String oSheet = "--Keywording--" Set oExcel = GetObject(, "Excel.Application") Set oExcel = GetObject(oSheet).Application ' oExcel.Visible = True ' ' With oExcel.Sheets(oSheet) ' ' .Select ' .Range("A1:G1000").Clear ' .Cells(1, 1).Select ' .PasteSpecial (xlPasteAll) ' .Cells(1, 1).Select ' ' End With — CES
Nobody wants to spend time decyphering your code presented in the comment above. Add the code to the question so that it can be read. — Variatus
Once a workbook is open it is a member of the Application.Workbooks collection. You can Activate it, like oExcel.Workbooks("MyWorkbook.xlsx").Activate. However, it seems like you confuse the Excel application, an Excel workbook and a workbook's worksheet. oExcel.Sheets(oSheet) can never work and neither can GetObject(oSheet).Application. Consider working without Activate and without Select. oSheet.Range("A1:G1000").ClearContents will work provided oSheet is set as a worksheet of an open workbook. — Variatus

ASH ASH · Accepted Answer · 2017-11-21T17:03:41

Sorry, but can't you just turn on the Macro Recorder and get what you want?

Sub TryThis()
    Windows("SecondaryWorkbook.xlsb").Activate
    Range("A1").Select
    Windows("PrimaryWorkbook.xls").Activate
    Range("A1").Select
End Sub

I believe you need to be in the same instance of Excel, or this wonr't work. AFAIK, different instances of Excel don't communicate with each other...at all.

Referencing Excel from a Word Macro when Excel and the file are already open?

1 Answers