Referring to an Active Excel Workbook in VBS

[Former Member]

I have a .vbs script which works great.  When it runs it opens an Excel workbook in these lines:

Set xcl = CreateObject ("Excel.Application")

Set wbk = xcl.Workbooks.open("C:\Users\Barry\Desktop\Level3\Defects.xlsm")

Set sht - wbk.Sheets("Sheet1")

I want wbk to refer to the active workbook instead (i.e. Set wbk = ActiveWorkbook) but it doesn't like that, gives me "Object Required: ActiveWorkbook"

Please advise

[script_man · Accepted solution]

Hi Stuart,

You can try the following:

set wbk = xcl.ActiveWorkbook

Regards,

ScriptMan

[Former Member · Accepted solution]

Thanks Script Man. I tried that, now I'm getting "Object required: 'wbk'

[script_man · Accepted solution]

In this case, one must refer to the existing Excel session.

For example:

set xcl = getObject( , "Excel.Application")

. . .

Regards,

ScriptMan

[Former Member · Accepted solution]

Works!  Thank you Script Man.