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")

Answer 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