CODEWITHSUNDEEP
excelobjectvbscriptcreateobject
SamHarper
SamHarper

Reputation: 105

GetObject works, CreateObject doesn't

Trying to write a script that

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

Upvotes: 1

Views: 1775

Answers (1)

Étienne Laneville
Étienne Laneville

Reputation: 5031

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

Upvotes: 1

Related Questions