How to run a macro only if the Excel workbook is open with VBScript?

Question

World!

I am trying to automate a report task at my job and I have the following situation:

I need to execute a macro on a workbook by using a script. I tried to write a vbscript to do the job, and this is the significant part of it:

Set objWbk = GetObject("***Path***\test.xlsm")

objWbk.Application.Run "test.xlsm!test" 

WScript.Quit

The macro runs perfectly. My real problem is that I only want to do the report only if the workbook is open.

Is there a way (in vbs or vba) to determine if that workbook is open ? (by the way, it is located on another computer on my network)

Do you want to run the macro if the workbook was already opened? Or do you want to run the macro after `GetObject()` finished opening the workbook? — Ansgar Wiechers, Sep 19 '12 at 18:38

Scott Holtzman · Answer 1 · 2012-09-19T17:20:20.353

1

This is not fully tested and may a need a bit of modification but see if it gets you what you need.

On Error Resume Next
Set objWbk = GetObject("***Path***\test.xlsm")

If Err.Number = 0 Then objWbk.Application.Run "test.xlsm!test" 

wScript.Quit

edited Sep 19 '12 at 17:20

answered Sep 19 '12 at 17:05

Scott Holtzman

27,099
5
37
72

score 1 · Accepted Answer · answered Sep 20 '12 at 14:24

Since you want to run the macro only when the workbook is already opened, something like this might work:

wbName = "test.xlsm"
wbFullName = "***Path***\" & wbName

Set xl = GetObject(, "Excel.Application")
For Each wb In xl.Workbooks
  If LCase(wb.Path & "\" & wb.Name) = wbFullName Then
    wb.Application.Run wbName & "!test"
  End If
Next

How to run a macro only if the Excel workbook is open with VBScript?

2 Answers2

Linked