How to Run a VBA Macro from Task Scheduler in Windows Server 2016

scheduled-tasktask-schedulerwindows-server-2016

I have a VBA macro for extracting some data daily. The Macro doesn't have any interactive component like a Message Box, Custom Inputs, or Input Box. It doesn't expect any user input, it only downloads some data from a DB and generates some CSVs.

My task is to automate its extraction using Windows' Task Scheduler.

I tried to create a VBS app to run it like here:

https://stackoverflow.com/questions/58139154/running-an-excel-macro-from-task-scheduler

It didn't work.

I created my own C# app to run it and didn't work.

At this point, I'm stuck because the VBS app and the C# app run the macro properly if I run it manually.

I have tried the following combinations:

  • Run only when the user is logged on. -> Successful.
  • Run whether user is logged or not. -> Failed.
  • Run whether user is logged or not. Plus, Run with the highest privileges. -> Failed.
  • Run whether user is logged or not. Plus, Do not store password … -> Failed.
  • Run whether user is logged or not. Plus, Do not store password … And Run with the highest privileges -> Failed.
  • Run whether user is logged or not. Plus, Run with the highest privileges and Hidden. -> Failed.
  • Run whether user is logged or not. Plus, Do not store password … and Hidden -> Failed.
  • Run whether user is logged or not. Plus, Do not store password …, Run with the highest privileges and Hidden -> Failed.

Also, I tried to schedule the VBA macro too and there was no difference (this was my first attempt).

Additionally, as I highlighted in the comments. I have tested with my account that is an Admin account and a service account that is already certain scheduled tasks.

Furthermore, the commands look like these ones:

  • C:\...\MacroRunner.exe
  • C:\...\MacroRunner.vbs

Both run in the CMD. Also, I have tried to run each of them manually. I didn't expect only that the scheduled time executed it.

Any idea what could I be doing wrong? Thanks for your time.

P.S.:

If you read the StackOverflow question, the person is facing the same issue, but he is looking for code. I'm personally convinced this is a Server issue, not a code issue.

Also, it's not the first place where you can find similar topics:

https://www.thespreadsheetguru.com/blog/how-to-automatically-run-excel-vba-macros-daily

Best Answer

I have tried the following combinations:

  • Run only when the user is logged on. -> Successful.

If the program only works when a user is logged on, it means it needs an interactive session.

The fact that it doesn't actually display any window when running doesn't matter: if something in the code needs an interactive session (even without your knowledge) and can't get it, it will fail. Since a VBA macro is by design something that runs inside an Office application (which under normal circumstances always has an UI), this is no surprising at all; unless the original coder took extra steps to allow the macro to run non-interactively, this is expected behavior.

We can't help you any further without the actual application code; I suggest you ask on StackOverflow how to make sure a VBA macro can run wihout an interactive session.

Related Topic