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

Federico Navarrete
  • 109
  • 7
  • 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
  • First of all, you should check whether Task Scheduler is failing to run your macro at all, or if the macro itself starts but then errors out. Does it have any sort of logging? If not, can you add it? – Massimo Nov 24 '20 at 16:36
  • @Massimo, I have some logs in the apps, but I'm not sure if I could add something in the macro. The Tasks in theory ran, but not the macro part. If you see the StackOverflow question, he was facing exactly the same issue writing some code and didn't get any answer. Personally, I don't think it's an issue about coding, but about the Server configuration that's why I asked the question here. – Federico Navarrete Nov 24 '20 at 16:38
  • What command line are you using in the Task Scheduler? Does it work if you run it from a command prompt? – Massimo Nov 24 '20 at 16:39
  • Hi @Massimo C:\...\MacroRunner.exe. – Federico Navarrete Nov 24 '20 at 16:43
  • Did you try to run the scheduled task manually, instead of waiting for it to run? When it fails, what is the error code? – Massimo Nov 24 '20 at 16:47
  • Hi @Massimo. Yes, I did. It only works under this specific combination: **Run only when the user is logged on.** – Federico Navarrete Nov 24 '20 at 16:48
  • Then it looks like it needs an interactive session; if this is the case, you can't run it from the Task Scheduler. Probably something in the code **really** wants to open a window, and fails if it can't. – Massimo Nov 24 '20 at 16:51
  • Hi @Massimo. When I run the macro, there is not any input box or message box. It just runs and downloads data. – Federico Navarrete Nov 24 '20 at 16:52
  • Sadly, this doesn't really matter; see my answer. – Massimo Nov 24 '20 at 16:59

2 Answers2

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.

Massimo
  • 68,714
  • 56
  • 196
  • 319
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.

Federico Navarrete
  • 109
  • 7