[SOLVED] Launching xlsm from MS encounters errors.

[Tourless]

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.

[Dorian (MJT support)]

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

[Tourless]

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]

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.

[Grovkillen]

You could run it through the "run" command perhaps?

[Tourless]

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

[Tourless]

[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!

[Dorian (MJT support)]

Aaah, that's great to hear. Thank you for the update.