1

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

SamHarper
  • 105
  • 1
  • 19
  • 1
    `CreateObject(, "Excel.Application")` -> `CreateObject("Excel.Application")` – Ansgar Wiechers Oct 04 '19 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 Oct 04 '19 at 14:33
  • If you want the instance to be visible you also need `objXLApp.Visible = True`. – Ansgar Wiechers Oct 04 '19 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 Oct 04 '19 at 15:42
  • 1
    "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 Oct 04 '19 at 16:24

1 Answers1

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
Étienne Laneville
  • 3,143
  • 3
  • 10
  • 26
  • Hi, this is not correct, this is creating a new session without even checking current sessions. – SamHarper Oct 04 '19 at 15:50
  • 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. – Étienne Laneville Oct 04 '19 at 15:58
  • 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. – J. Woolley Sep 29 '20 at 16:59