Macro having issues when run on Windows Scheduler

[faeren]

I have a macro in microsoft access that I have set-up on a windows scheduler. It's supposed to run every morning to load data before I come in. When I run the code while I'm logged into the computer the code runs fine, but when it runs on the scheduler I always get the following error:



error code: 1004
Description: Microsoft Office Excel cannot access the file 'C:\MLX Zipped Files\Master Trade Date Activity 2222_500448_4636_PP.XLS'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

For some reason the code always hangs on saving the excel file changes that have been. The following code line:

eWBK.SaveAs sDestination, xlOpenXMLWorkbook, "", "", , , xlNoChange, xlLocalSessionChanges

I don't understand why it has trouble saving this file when running on the macro, because when I kick off the code it runs fine.

[pbaldy]

When I've had these types of problems it's been a permissions issue. You say it runs fine when you're logged in but not on the scheduler. Does that mean the computer isn't logged in when it runs that way? If not, have you tried locking the workstation rather than logging out and seeing if it runs correctly then?

[faeren]

When I kick off the code to run in the code window it runs fine. If I run the macro to start the code it works, but If I run the code from the scheduler it errors out on saving the excel sheet everytime. I've tried running the scheduler when I'm logged in, I've tried logging out, locking the computer, unlocking the computer and running the code while I'm logged. I've tried checking the library references, I've had permissions changed on the computer so that I'm the administrator. I've done everything I can think of. The code works fine when I log in and run it from the code window in access, but won't save the excel file when run on the scheduler.

[faeren]

I finally found the issue. I came across an old post while googling that someone was having the same issue when Office COM scripting involved saving office files.

The scheduled tasks that wouldn't function all involved Office COM scripting where I'd save off Excel and Word files. I fixed the Excel scripts by creating a folder called
c:\windows\syswow64\config\systemprofile\desktop. This desktop folder wasn't present before and I googled across a posting that it had to be there in order for the Excel COM function to work as a scheduled task. Without this desktop folder the Excel COM would fail while trying to perform a SaveAs() to write out the final document.

I created this folder and now the scheduler runs fine.

[pbaldy]

That's a new one on me, and thanks for posting back with the solution. Glad you got it sorted out.