We need to research our options of automating Excel using a VBS script launched under Task Scheduler. The problem we see is that the script runs when user is logged on, and does not run when we configure the script to "run whether user is logged on or not".
We'll start this project by first reproducing the problem and documenting the setup. Apparently, a customer is trying to automate Microsoft Excel by launching Excel workbook from a Visual Basic script, and then doing something with it.
Let's create a sample C:\tmp\test.xlsx workbook with almost nothing in except for some text in A1 cell in Sheet1. Working with the file manually occurs as inspected, we can open and close worksheet without trouble.
Let's now experiment with Task Scheduler.
Everything is good so far. The first problem starts when we change the property of a task to "Run whether user is logged on or not". In this case the Notepad app starts, but is not visible on our desktop, so it is hidden and we cannot do anything about it but kill the process in Task Manager. When we kill it, our Task Scheduler task returns from the Running to Ready state, and is able to launch notepad again.
In theory, everything still kind of works, Task Scheduler starts notepad when we instruct it to. But we don't see the UI, and apparently can't do anything interactive about the application such as closing a dialog box that might appear on a hidden desktop.
Our next step is to launch an Excel workbook. These steps work:
Now, what happens when we change the task to "Run whether user is logged on or not"? Well, another instance of "Microsoft Excel (32 bit)" appears in Task Manager, but only briefly for about 20 seconds, when we run a task. Apparently, there is a problem, as this behaviour is different from notepad. It looks like Excel tries to tell us something with a dialog box, but we cannot see it, and have no idea what the problem might be.
So, why can't we use unattended Excel automation using the "Run whether user is logged on or not" setting in Microsoft Task Scheduler?
Long story short - Microsoft apparently does not support it and actually discourages us to use it, as I write it in August, 2015.
The root cause of the problem is that Office is an interactive application that may display dialog boxes and other stuff requiring user to take action by clicking something, dismissing dialog box, etc. Whenever you run a thing unattended under "Run whether user is logged on or not" there is no user interface. Meaning that the app runs with a hidden desktop, we cannot do anything about it. For example, we cannot dismiss an error message, or do anything else.
Hacks existed before to make it working, but it no longer works, it seems. The KB article is removed from Microsoft website. Also, they actively discourage using Excel automation in traditional sense on the server.
For this specific client, historically, there was another Office automation task running, something to do with Outllook obtaining RDL reports from MSSQL reporting server.
In the end, we re-configured the Excel automation task to run interactively in the same desktop and under the same account, who must be logged on anyway for Outlook automation to work.