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: enter image description here

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:

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?

2

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.

  • 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 Jul 23 '18 at 7:58
  • @farosch Read over stackoverflow.com/questions/18389562/… for help with the next for multiple solutions to that error. – Pimp Juice IT Jul 23 '18 at 22:37
  • @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 Jul 23 '18 at 23:36
  • @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 Jul 24 '18 at 12:40
  • @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 Jul 24 '18 at 12:42
0

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:

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.