Getting Excel to work in Scheduled Tasks

It's been a while since I've had to post any voodoo, but this one I thought was worth mentioning.

Sometimes, when you're trying to turn data into information in a BI environment, you have to suck it up and access spreadsheet data (*shudder*). If that data is not clean, you may need to run a macro to export clean data to another file, massage it, and then load that.

Since we want to automate this process, we need to:

a) Make sure we run the code in the Workbook_Open() event of ThisWorkbook
b) Turn off all the security alarms that stop it running automatically
c) Set up a scheduled task.

The problem is that even when you have done that, Excel really doesn't want to run in a non-interactive environment. More than likely your scheduled task will fail silently.

To get around that, create these two folders on the server that is running the task.

C:\Windows\System32\config\systemprofile\Desktop  

C:\Windows\SysWOW64\config\systemprofile\Desktop

For some reason, Excel wants these folders to exist when it is being run non-interactively.

Try again, and all things being equal, your code should work.

Remember to close Excel as the last line in your VBA code to avoid blowing up the servers memory over time.