Unable to run batch starting Excel as scheduled task

Question

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?

Answer 1

Your VBA script is failing because xlBook is not the correct object to support the Run method.

Try

xlApp.Run "GENERATE_REPORTS"

Alternatives to above

• If your macro is in a module xlApp.Run "BSYSTEM.xls!GENERATE_REPORTS" 'notice the format of 'workbookName'!macro

• If your macro is in a sheet 'xlApp.Run "'BSYSTEM.xls'!sheet1.GENERATE_REPORT"

File Extensions

.xla is an Excel Add-In file format, you could try saving it as an .xlam which is a macro-enabled format of xla.

Code Breakdown

set xlApp = CreateObject("Excel.Application") 'Creates the Excel Application process.

xlApp.Visible = True 'Enables the application to be viewed/edited.

set xlBook = xlApp.Workbooks.Open("X:\SYSTEM\MAKROS\BSYSTEM.XLA") 'Opens a specified workbook within the Excel Application that was just opened.

xlApp.Run "GENERATE_REPORTS" 'Runs the macro command GENERATE_REPORTS.

xlBook.Close 'Closes the workbook.

xlApp.Quit 'Ends the Excel Application Process.

Answer 2

Since with this command Excel needs to display a UI the task is unable to run in the Background which is automatically the case when Run whether user is logged on or not is selected in the task properties. After setting this option everything worked as expected:

For the Task to be able to be executed after a restart of the server I enabled automatic login with this tool.