Error running Excel Macro VBScript

Question

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'

As stated in @ManishChristian's answer, the CreateObject syntax is wrong in your code. This leads to the error. And even if you offer a bounty of +1000000000 there is no other answer possible until you are correcting the syntax and tell us the error which is thrown using the correct syntax. — Axel Richter
– Axel Richter, Commented Mar 13, 2017 at 9:43
As stated in my response I had been using correct syntax but in attempt to resolve the error I made the change. I have edited the code in my question to what I now have and I am still recieving an error. Please see edited question. — Eoin2211
– Eoin2211, Commented Mar 13, 2017 at 9:46
See stackoverflow.com/questions/41405937/…. Try CreateObject("Excel.Application.16") or CreateObject("Excel.Application.15") or .14 or .12 dependent of your Excel version. See rondebruin.nl/win/s9/win012.htm. — Axel Richter
– Axel Richter, Commented Mar 13, 2017 at 9:58
I tried this and got the following error Microsoft VBScript runtime error: ActiveX component can't create object: 'Excel.Application.15' Looks to be the same issue? — Eoin2211
– Eoin2211, Commented Mar 13, 2017 at 10:23
I eventually found that the machine that was being used by the Autosys job does not have Microsoft Office installed. Thanks for your help — Eoin2211
– Eoin2211, Commented Mar 13, 2017 at 15:14

Community · Accepted Answer · 2017-05-23 12:25:47Z

2

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

Set xlApp = CreateObject("Excel.Application")

Check this answer for more details.

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Mar 10, 2017 at 15:37

ManishChristian

3,7944 gold badges25 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Eoin2211 Over a year ago

Thanks, I had been using Set xlApp = CreateObject("Excel.Application") originally, and that's when I got the error. This also doesn't work for me

ManishChristian Over a year ago

First check the answer from the link that I've shared. If your Workbook is already open than use GetObject, but if Workbook is closed use CreateObject. What is the error you are getting?

Eoin2211 Over a year ago

Workbook is closed. The error I am getting with this code is Microsoft VBScript runtime error: ActiveX component can't create object: 'Excel.Application'

ManishChristian Over a year ago

This one is different error and for that you must allow system access.In your macro module at right down of screen you should choose allow system access.

Eoin2211 Over a year ago

I can't see this option? Could you explain in more detail how to do this?

|

Eoin2211 · Accepted Answer · 2017-03-13 15:13:56Z

0

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.

answered Mar 13, 2017 at 15:13

Eoin2211

9353 gold badges21 silver badges49 bronze badges

Comments

Chad Crowe · Accepted Answer · 2017-03-17 19:21:33Z

0

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.

answered Mar 17, 2017 at 19:21

Chad Crowe

1,3381 gold badge16 silver badges21 bronze badges

Collectives™ on Stack Overflow

Error running Excel Macro VBScript

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related