Silicon Thumb

Since the 1980s, I've worked with computers, watched them grow, shrink, change and improve. I've worked with a lot of users and solved a lot of problems in that time too, so I thought this would be a good place to share some of the random things I've found and solved. If you have some odd problem, email me. If I can figure it out I'll post the answer here.

My Photo
Location: Mansfield, Texas, United States

I am a veteran computer geek, but I prefer the term 'Hired Gun', since that gives the (misleading) impression that I know what I'm talking about. I have worked on all sizes of system as an engineer, developer, technical support and operations, and at all levels from Operator to CIO.
I have some certifications, but what they are depends on what Microsoft is calling them this week.

If you have a question, and don't mind the answer being posted, email me here, removing the spam stopper.

Friday, October 21, 2016

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.


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.