[SOLVED] Launching xlsm from MS encounters errors.

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

[SOLVED] Launching xlsm from MS encounters errors.

Post by Tourless » Thu Apr 25, 2019 1:25 pm

Hi Folks,

As the title says, I'm running into a problem with a .xlsm file that is called from my script. I've tried using ExecuteFile, RunProgram, and the XLOpen functions and each time the file launches but then encounters an error as it runs. If I run the xlsm file directly or from another scheduling application, it runs fine and completes its tasks without error. So I'm thinking this might have something to do with permissions? I'm already running MS as admin, and I don't see any parameters on any of the functions I've tried that seems to speak to elevated permissions, so I'm at a loss.

Anyone else run into problems with an xlsm file that encounters errors when it's called from Macro Scheduler, and if so, how did you resolve it?

In case the detail of the xlsm file is pertinent, when the file opens it calls an excel vba macro routine that copies and renames a few files, creates a report, saves it, and emails a copy of the report via outlook. The failure when launched from my script occurs at the emailing stage and states ActiveX component can't create object. I've examined the vba run-time error (429) but I don't find the solutions applicable as my code is fine and this file always runs without error when not launched from within a macro scheduler script.

thanks in advance.
Last edited by Tourless on Mon Jun 10, 2019 1:48 pm, edited 3 times in total.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Launching xlsm from GS encouters errors.

Post by Dorian (MJT support) » Thu Apr 25, 2019 5:11 pm

Hi, we'd be happy to help. Let's see if we can get to the bottom of it.

What is GS? What error are you seeing?

Can you share the pertinent part of your code please?

The following is the code for opening an xlsm and running an Excel Macro using XLOpen and XLRun

Code: Select all

//Set up the document path
let>filepath=C:\Users\xb360\Downloads\test.xlsm

//Open the file. Notice the handle variable "HandleForMacroScheduler"
XLOpen>filepath,1,HandleForMacroScheduler

//Wait for the sheet to open, and set focus
WaitWindowOpen>test*

//Now we use the handle set up in XLOpen, plus the name of the Excel Macro
XLRun>HandleForMacroScheduler,XLMacroTest

Yes, we have a Custom Scripting Service. Message me or go here

Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Re: Launching xlsm from GS encouters errors.

Post by Tourless » Thu Apr 25, 2019 5:24 pm

Sorry, that was supposed to read 'MS encounter errors' for Macro Scheduler of course. Apparently it was too early for my proof reading eyes. Anyway based on your reply can I assume the problem has to do with the way I handle the code within my excel file?

My Macro Scheduler script code is simply as follows...

Code: Select all

ExecuteFile>C:\Reports\My File.xlsm,
Now when my excel file is open, the Workbook Open event calls a CreateReport macro which goes through it's motions and fails on the following line...

Code: Select all

Set OutApp = CreateObject("Outlook.Application")
That code works fine when I launch the file either by double clicking or opening it from Task Scheduler, for instance.

I'll try implementing the code you outlined below and let you know how I make out.

Thanks.

Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Re: Launching xlsm from GS encouters errors.

Post by Tourless » Thu Apr 25, 2019 5:32 pm

Nope. Same failure in the same place. To implement your method I removed the CreateReport call from the Workbook Open event, and made the call using XLRun function. It indeed ran but again errors out at the same line of code I use to create the Outlook object.

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: Launching xlsm from GS encouters errors.

Post by Grovkillen » Thu Apr 25, 2019 5:36 pm

You could run it through the "run" command perhaps?
Let>ME=%Script%

Running: 15.0.24
version history

Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Re: Launching xlsm from GS encouters errors.

Post by Tourless » Thu Apr 25, 2019 6:33 pm

I'm not seeing a 'Run' command in the MS command reference. Can you elaborate?
UPDATE: Nevermind, I see what you did there. :P

Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Re: [SOVLED] Launching xlsm from MS encounters errors.

Post by Tourless » Thu Apr 25, 2019 6:55 pm

[SOLUTION]

All things being equal, I attempted to run excel as admin, tried RunProgram with RP_ADMIN=1, and still failed. Some interweb posts about the specific VBA error (429) proved useful as a quick Office repair seems to have taken care of the issue. My final solution was Dorians...

Code: Select all

Let>filepath=C:\Reports\My File.xlsm
XLOpen>filepath,1,hForMS
WaitWindowOpen>My File.xlsm
XLRun>hForMS,CreateReport
exit
Done and done. Thanks for the support here guys!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: [SOVLED] Launching xlsm from MS encounters errors.

Post by Dorian (MJT support) » Fri Apr 26, 2019 12:09 pm

Aaah, that's great to hear. Thank you for the update.
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts