GetObject works, CreateObject doesn't

Question

Trying to write a script that

If excel session already open, join it to this one,
Excel session not open, thus create one.

First option works splendid! Second option doesn't work and does not give any explanation, just quits and does nothing!

Dim objXLApp, objXLWb, objXLWs
Dim XLWasRunning

XLWasRunning = True

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

If Not TypeName(objXLApp) = "Empty" Then
    strMessage = "Excel Running."
Else
    strMessage = "Excel Not Running."
    Set objXLApp = CreateObject("Excel.Application")
End If

Set objXLWb = objXLApp.Workbooks.Open("F:\GFD\Sam\Test\test.xlsx")
Set objXLWs = objXLWb.Sheets(1)

EDIT : changed CreateObject(, "Excel.Application") to CreateObject("Excel.Application")

CreateObject(, "Excel.Application") -> CreateObject("Excel.Application") — Ansgar Wiechers
– Ansgar Wiechers, Commented Oct 4, 2019 at 14:01
Still nothing happens, also If I put a message box just after the ELSE I don't even get a message box. — SamHarper
– SamHarper, Commented Oct 4, 2019 at 14:33
If you want the instance to be visible you also need objXLApp.Visible = True. — Ansgar Wiechers
– Ansgar Wiechers, Commented Oct 4, 2019 at 15:12
I have changed it to objXLApp.Visible = True and it still doesn't work. Also when I run it with excel open, the cmd prompt stays open screen 4-5 seconds. When I don't have excel open, its on screen for a bout a tenth of a second? — SamHarper
– SamHarper, Commented Oct 4, 2019 at 15:42
"It doesn't work" is not a valid problem description. Please edit your question and describe the problem in more detail. Is an Excel process created? Does the Window show? Are you getting errors? — Ansgar Wiechers
– Ansgar Wiechers, Commented Oct 4, 2019 at 16:24

Étienne Laneville · Accepted Answer · 2019-10-04 16:01:49Z

1

This code, based on the comments you received, should work:

Dim objXLApp, objXLWb, objXLWs
Dim XLWasRunning
Dim strMessage

' Get running instance
Set objXLApp = GetObject("", "Excel.Application")

If Not objXLApp Is Nothing Then
    strMessage = "Excel Running."
    XLWasRunning = True
Else
    strMessage = "Excel Not Running."
    Set objXLApp = CreateObject("Excel.Application")
    XLWasRunning = False
End If

objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("C:\Temp\test.xlsx")
Set objXLWs = objXLWb.Sheets(1)

You might want to add the following to check if you should load the workbook again:

If Not XLWasRunning Then
    Set objXLWb = objXLApp.Workbooks.Open("C:\Temp\test.xlsx")
    Set objXLWs = objXLWb.Sheets(1)
End If

If Excel is already running you can also check if your Workbook is already loaded before loading it:

Dim bWorkbookFound
bWorkbookFound = False
For Each objXLWb In objXLApp.Workbooks
    If objXLWb.Name = "test.xlsx" Then
        ' Workbook already loaded
        bWorkbookFound = True
        objXLWb.Activate
    End If
Next

edited Oct 4, 2019 at 16:01

answered Oct 4, 2019 at 15:44

Étienne Laneville

5,0815 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SamHarper Over a year ago

Hi, this is not correct, this is creating a new session without even checking current sessions.

Étienne Laneville Over a year ago

If you mean that it opens up a new Excel window, that's because you are opening the Workbook again at the end of your code. You can check XLWasRunning and skip loading the Workbook.

J. Woolley Over a year ago

In VBScript, I believe Set objXLApp = GetObject("", "Excel.Application") always creates a new instance. It is the same as Set objXLApp = CreateObject("Excel.Application"). To connect to an existing instance, use Set objXLApp = GetObject( , "Excel.Application"). Do NOT use Set objXLApp = WScript.GetObject( , "Excel.Application"), which will complain about the missing argument.

Collectives™ on Stack Overflow

GetObject works, CreateObject doesn't

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related