2
I have the following Skript that was previously run manually:
set DIR="X:\SYSTEM\MAKROS"
X:
cd %DIR%
start /wait EXCEL.EXE /e-GENERATE_REPORTS BSYSTEM.XLA
When manually started, this works just fine. The BSYSTEM.XLA
is part of an Excel-Based project management system, so I cannot export the file to a different file format.
I now need to run this script on a daily basis. When creating a scheduled task to run this .bat
-file it simly doesn't work. The task ends whithin several seconds (the script normally takes about 10 minutes to complete) with the last run result code 0x1
and the history of the task doesn't show anything helpful:
I've already tried to create a task that directly runs "C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE" /e-GENERATE_REPORTS BSYSTEM.XLA
but this just errors out with this being shown in the history:
Task Scheduler failed to launch action "C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE" in instance "{1fbeadd0-605b-4fe0-8d96-621281c53519}" of task "\Allgemein\TMG Bericht". Additional Data: Error Value: 2147942667.
I've also tried a vbs script like this:
set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
set xlBook = xlApp.Workbooks.Open("X:\SYSTEM\MAKROS\BSYSTEM.XLA")
xlApp.Run "GENERATE_REPORTS"
xlBook.Close
xlApp.Quit
But this way I get the error The macro can not be executed. The 'GENERATE_REPORTS' macro may not be available in this workgroup, or all macros have been disabled
, which is not true since its working fine with the obove script being called manually. The network drive from which the file is strated is set as a "trusted location".
Strangely enough when I open BSYSTEM.XLA
I don't see any sheets, just an excel file with mostly all elemnts greyed out except the makro button and when trying to take a look at the macros I dont see any. Thats why I'm struggling to understand what start /wait EXCEL.EXE /e-GENERATE_REPORTS BSYSTEM.XLA
even does..
I couldn't really find anything helpful about Excel command line switches except these two documents:
- https://support.office.com/en-us/article/command-line-switches-for-microsoft-office-products-079164cd-4ef5-4178-b235-441737deb3a6
- https://support.microsoft.com/de-de/help/291288/description-of-the-startup-switches-for-excel
What can I do to provide a scriptable alternative to the above btach script so that I can run this command as a scheduled task, or what can I do to fix the vbs?
This gets me one step further: The error I now get is:
The macro can not be executed. The 'GENERATE_REPORTS' macro may not be available in this workgroup, or all macros have been disabled.
– farosch – 2018-07-23T07:58:37.990@farosch Read over https://stackoverflow.com/questions/18389562/cannot-run-the-macro-the-macro-may-not-be-available-in-this-workbook for help with the next for multiple solutions to that error.
– Pimp Juice IT – 2018-07-23T22:37:37.767@farosch is your macro in a module or in one of the sheets? Also worth checking the following link on how to enable macros in office files.
– angelofdev – 2018-07-23T23:36:00.780@PimpJuiceIT All settings in the trust center are set. When being manually called via the batch script it works, so this cant be the problem. Also added this info to my question – farosch – 2018-07-24T12:40:48.653
@angelofdev I don't see any sheets when opening the
BSYSTEM.XLA
, just an Excel file with all elements greyed out. The macro overviw doesn't show any macros either.. I also added this Info to the initial question – farosch – 2018-07-24T12:42:13.007@farosch I wanted to mention that if task scheduler is running the batch under a different security context that you type in the credential when you save the job and not the same credential as the user that's logged on which you set those "trust center" settings, then you will want to sign onto that machine as that account and set those trust settings with its account by the way. See https://superuser.com/questions/1214736/windows-10-scheduled-tasks-with-workstation-lock-unlock-not-being-triggered/1217125#1217125 for further tips with task scheduler just in case too.
– Pimp Juice IT – 2018-07-24T12:46:05.767@PimpJuiceIT just checked: I am logged in as our domain service user kue\service and the task is running as the same user also. – farosch – 2018-07-24T16:20:36.870