CODEWITHSUNDEEP
excelvbscript
Eoin2211
Eoin2211

Reputation: 911

Error running Excel Macro VBScript

Running a job from AutoSys and I am getting an error. VBS runs an excel macro. VBS code :

Option Explicit

Dim xlApp, xlBook
Set xlApp = CreateObject("Excel.Application")
On Error Resume Next
set xlBook = xlApp.Workbooks.Open("Z:\Confidential Restricted\Weekly_HR_Employees_Macro.xlsm",0, False)
xlApp.Run "Weekly_HR_Employees_Macro.Weekly_HR_Employees_Macro"
xlBook.Close True
xlApp.Quit

set xlBook = Nothing
Set xlApp = Nothing

Error: 

Microsoft VBScript runtime error: ActiveX component can't create object: 'Excel.Application'

Upvotes: 3

Views: 2002

Answers (3)

Chad Crowe
Chad Crowe

Reputation: 1338

You can use GetObject("Excel.Application"), but you need to make sure you open up an instance of excel before you use it. GetObject will get a reference to this open instance of Excel and let you use that.

Upvotes: 0

Eoin2211
Eoin2211

Reputation: 911

Although the script runs on my machine, it would not run on the machine that the AutoSys job was using. I eventually found that the machine that was being used by the Autosys job does not have Microsoft Office installed.

Upvotes: 0

ManishChristian
ManishChristian

Reputation: 3784

You are using GetObject syntax with CreateObject method. You need to use: 

Set xlApp = CreateObject("Excel.Application")

Check this answer for more details.

Upvotes: 2

Related Questions