CODEWITHSUNDEEP
excelvbscriptcom
Christoph Fetkenheuer
Christoph Fetkenheuer

Reputation: 3

Opening or activating workbook in Excel from VBS

I am writing a small VBScript to do the following:

  1. Check if Excel is open; if not open it.
  2. If Excel is open, check if a specific workbook is open.
  3. If workbook is open, make it active; if not, open it.

So far I have been able to write the following code:

ExcelFileName = "....xlsx"

On Error Resume Next
Set xl = GetObject(, "Excel.Application")
IF Err Then
    If Err.Number = 429 Then
    WScript.Echo "Workbook not open (Excel is not running)."
    Else
    WScript.Echo Err.Description & " (0x" & Hex(Err.Number) & ")"
    End If
    WScript.Quit 1
End If
On Error Goto 0

Set wb = Nothing
For Each obj In xl.Workbooks
    If obj.Name = ExcelFileName Then
    Set wb=obj
    xl.DisplayAlerts = False
    wb.Save
    Exit For
    End If
Next

If wb Is Nothing Then
    xl.Workbooks.Open("C:\...")
End If

Set xl = Nothing
Set wb = Nothing

But if Excel is not already open, it silently fails to open a new instance.

Upvotes: 0

Views: 9274

Answers (2)

user4883543
user4883543

Reputation:

Use GetObject to open the file.

set wb = GetObject("c:\folder\excel.xls")

COM will work out what needs to be done. That one line is all you need for your three requirements.

This is the guts of what VB asks COM to do when using GetObject with a filename.

BindMoniker

Locates an object by means of its moniker, activates the object if it is inactive, and retrieves a pointer to the specified interface on that object.

HRESULT BindMoniker( LPMONIKER pmk, DWORD grfOpt, REFIID iidResult, LPVOID FAR * ppvResult );

Upvotes: 2

Tarik
Tarik

Reputation: 11209

GetObject behaves normally in giving you a running instance of Excel or causing an error. In case you get an error (Excel not running), use CreateObject to create a new instance of Excel.

Upvotes: 1

Related Questions