0

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

14
  • Can the -1s provide me some input? I'm not asking for some C# or VBS code since my code apps work properly and the macro run properly. If I was searching for code, I'd have asked in Stack Overflow, which is not my task. It's Task Scheduler, the one that cannot run them. – Federico Navarrete Nov 24 '20 at 16:24
  • There are lots of possible reasons why this doesn't work. Two ones off the top of my head: which user account are you scheduling the task to run as? And, is it able to run without actually opening an Excel window? – Massimo Nov 24 '20 at 16:31
  • Hi @Massimo. Thanks for your answer. I have tried with two accounts. My account that is an Admin account and a Service account that already runs other tasks. How can I test the second process? Do you have any idea? – Federico Navarrete Nov 24 '20 at 16:32
  • Also, scheduling a VBA macro to run a data extraction? In year 2020? If I didn't know it's sadly all too real, I'd guess it was some sort of joke... – Massimo Nov 24 '20 at 16:33
  • Hi @Massimo. I didn't write the Macro. I understand your point, but it's an old process and a very complex macro. I don't think I'll get the funds to re-write it. So, it's not a joke. It's a real case scenario. I wouldn't expose myself to get negative points if I didn't have this case. I have a good reputation in Stack Overflow. – Federico Navarrete Nov 24 '20 at 16:34
0

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.

0

I found how to fix my problem. I was found this old answer in Super User by squillman:

https://superuser.com/a/579901

  1. Open Component Services (Start -> Run, type in dcomcnfg)
  2. Drill down to Component Services -> Computers -> My Computer and click on DCOM Config
  3. Right-click on Microsoft Excel Application and choose Properties
  4. In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click OK

Now, what else I needed to do?

  1. Configure the scheduled task with the same account that I configured in step 4. I tried with the service account and failed.
  2. Run whether user is logged or not.

After this, I was able to run it properly.

Thanks, @Massimo for the hint.

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.