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, Mar 13 '17 at 09: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, Mar 13 '17 at 09:46
See http://stackoverflow.com/questions/41405937/active-x-error-with-excel-2016-and-late-binding#comment70015740_41405937. Try `CreateObject("Excel.Application.16")` or `CreateObject("Excel.Application.15")` or `.14` or `.12` dependent of your Excel version. See http://www.rondebruin.nl/win/s9/win012.htm. — Axel Richter, Mar 13 '17 at 09: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, Mar 13 '17 at 10:23
Have you checked whether the registry entry `HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application` and/or `HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application.15` exists? — Axel Richter, Mar 13 '17 at 14:30
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, Mar 13 '17 at 15:14

score 2 · Answer 1 · edited May 23 '17 at 12:25

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 '17 at 12:25

Community

1
1

answered Mar 10 '17 at 15:37

ManishChristian

3,759
3
22
50

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 – Eoin2211 Mar 10 '17 at 15:39
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? – ManishChristian Mar 10 '17 at 15:42
Workbook is closed. The error I am getting with this code is Microsoft VBScript runtime error: ActiveX component can't create object: 'Excel.Application' – Eoin2211 Mar 10 '17 at 15:43
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. – ManishChristian Mar 10 '17 at 15:45
I can't see this option? Could you explain in more detail how to do this? – Eoin2211 Mar 10 '17 at 15:52
Try: `Open Excel -> Excel optios -> Trust Center -> Trust Center Settings ->Macro Settings(from Trust Center window) -> Check Trust access to the VBA project object model`. – ManishChristian Mar 10 '17 at 15:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137769/discussion-between-manishchristian-and-e-mcandrew). – ManishChristian Mar 10 '17 at 15:55

score 0 · Accepted Answer · answered Mar 13 '17 at 15:13

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 '17 at 15:13

Eoin2211

911
2
19
39

score 0 · Answer 3 · answered Mar 17 '17 at 19:21

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 '17 at 19:21

Chad Crowe

1,260
1
16
21

Error running Excel Macro VBScript

3 Answers3