How can I open Excel from another Office Application without setting references?

Question

From Word and Outlook, I'm trying to open open Excel using GetObject method but I need to set the references first. I receive "Compile error: User-defined type not defined". In order to manually fix this, I need to go to Tools>References>Microsoft Excel XX.X Object Library. Is there a way to not have to do the manual step of setting the reference?

I access the same files from different sessions (local session, citrix, VM ware) that have different versions of Excel.

https://docs.microsoft.com/en-us/office/vba/excel/concepts/working-with-other-applications/controlling-one-microsoft-office-application-from-another

Code so far

Sub Macro1()
'
    Dim Excel As Object

    Dim wkbkXLBook As Excel.Workbook
    Dim wkSheet As Excel.worksheet

    Set Excel = GetObject(, "Excel.Application")

    If Excel Is Nothing Then

        MsgBox ("Excel was not found")

    End If

    Selection.WholeStory
    Selection.WholeStory
    Selection.Copy
End Sub

Have you tried a CreateObject("Excel.Application") instead of GetObject? — fbueckert
I think that your problem is coming from the Excel.xxxx in variable declaration. As with Excel object, dim as object and try??? — Nathan_Sav
@fbueckert arguably CreateObject could be used in the If Excel Is Nothing branch, to create a new instance when an existing one doesn't exist — Mathieu Guindon

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-01-30T15:52:17

If you're not adding the reference to the type library, you can't use the early-bound types defined in that type library.

Dim wkbkXLBook As Excel.Workbook
Dim wkSheet As Excel.worksheet

Excel is the programmatic name of the Excel type library, and Workbook is the name of a class defined in that library. Same for Worksheet. Since Excel isn't referenced, VBA can't resolve these types, and you get a compile error.

You need to work with late-bound code, i.e. in the dark, without IntelliSense, autocompletion, or parameter quick-info, and without making any typos - lest you run into run-time error 438 and 1004.

"Late-bound" means "resolved at run-time". Whenever you declare something As Object, that's precisely what happens:

Dim wkbkXLBook As Object
Dim wkSheet As Object

You can't use any of the Excel types unless you reference the Excel type library. That includes any xl* constant, too.

Dim Excel As Object

I'd warmly recommend renaming this to e.g. xlApp.

Watch out for implicit object references:

Dim someRange As Object
Set someRange = xlApp.ActiveWorkbook.Worksheets("Sheet1").Range("A1")

The above will work, but will also leak the ActiveWorkbook object, its Worksheets collection, and the Worksheet object retrieved; these leaked objects can (and often do) prevent the EXCEL.EXE process from correctly shutting down, even after executing xlApp.Quit and Set xlApp = Nothing: avoid double-dot referencing objects like this. Do this instead:

Dim books As Object
Set books = xlApp.Workbooks

Dim wb As Object
Set wb = books("workbook name")

Dim wbSheets As Object
Set wbSheets = wb.Worksheets

Dim ws As Object
Set ws = wbSheets("sheet name")

Dim rng As Object
Set rng = ws.Range("A1")

With every object involved explicitly scoped to the local procedure, everything should be fine.

How can I open Excel from another Office Application without setting references?

1 Answers