This VBA code fails (but no error) when run as scheduled task but not when run normally.

This one has me baffled.  When this code runs (in MS Access 2010/Windows 7) as a normal process that I launch, it works perfectly.  When run as a scheduled task (at 1AM under my user ID), this section fails.  I don't get an error on the Set xlWB line, but I get an error when I try to do anything with the xlWB object.

Here's the code:

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim strDoc as String
    strDoc = "C:\XYZ\MyExcelFile.xls"
    Set xlApp = New Excel.Application
    MySleep 2  ' Delay 2 seconds with DoEvents in the loop
    Set xlWB = xlApp.Workbooks.Open(strDoc) ' < --- This fails, but does not return an error
    MySleep 2
    Set xlSht = xlWB.Worksheets(1) ' <---- I get the error here
    With xlSht
        .Columns("A:B").HorizontalAlignment = xlCenter
    end with

The strDoc file is created earlier by a DoCmd.OutpuTo command.

I have checked the contents of the folder where strDoc is placed.  The file was identified by checking for it within earlier code.

I've tried referencing the actual sheet name instead of the index number with no luck.

I have set permissions on the folder to "Everyone".  I Examined the file after the fact and it shows it as such.

The database contains a reference to the Excel 14.0 Object library.
SMAHelpDeskIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Running out of task scheduler can be problematic at times depending on the operation.

One of the things that's not obvious is that even though you do a "run as", if the account is not logged on, there is no current desktop or profile, so the environment is not the same as when the job is running with the user logged in.  This means that things like mapped drives or printers may not be available.

Log your self in on the machine and try running the job out of task scheduler while logged in.  If the job runs, then as it stands, the job will only run in a logged in state.

Dale FyeCommented:
How are you running this unattended?  Are you logged into your machine and just have a timer event checking the system time and then running it, or are you using windows scheduler?

From your explanation, it sounds like you created this XL file on the fly (using OutputTo), so you shouldn't be encountering any XL macro trust issues.

Does your Access routine contain any error handling?  If so, instead of writing to the immediate window, you might want to write any errors that occur to a table that you can review post processing.  If not, I would add some.
SMAHelpDeskIT ManagerAuthor Commented:
JD.  Good suggestion.  I'll test the process by "Running" it from inside schedular. (The XLS file does reside on the C: drive and my MDB is in "My Documents").  If not for nothing, I won't have to schedule retries for "two minutes from now" - which is what I was doing all day yesterday. :-)

FY.  This section of code is but a small snippet of hundreds of lines of code with full logging.  That is how I identified where the problem is.  In fact, while debugging this, I had six lines of code after each line to record (in a table) what I was doing.  Good comment for a novice user.  


I will report back with the results...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SMAHelpDeskIT ManagerAuthor Commented:
JD - your help tip is a good idea.  I "Ran" the task and it failed - so no more having to set the task for two minutes from now.  Thanks...

Now, if only the process would succeed!
SMAHelpDeskIT ManagerAuthor Commented:
Sorry FY, I forgot to answer your first question.  I am using Task Schedular to run he process as an unattended task.  I gavethe task schedular my User ID and password which it uses to launch the process.
SMAHelpDeskIT ManagerAuthor Commented:
And further clarification:  The process does a number of things, including giving me a different spread sheet that shows the tracking of the entire process from begining to end.  It is quite involved.  (This is how I know it ran.  Sending the Process Tracking email is the last step.)
Dale FyeCommented:
What happens if you create a form that has a timer interval set to something like 60,000 and a timer event that checks the time and runs the process at that time:
Private Sub Form_Timer()

    If TimeValue(Now()) > #9:30:00 AM# Then
        Me.TimerInterval = 0
        Call yourprocedure
    End If
End Sub

Open in new window

You would of course have to be logged in for this to run, but it should run, even if your computer is locked.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple of things:

1. On:

" I don't get an error on the Set xlWB line, but I get an error when I try to do anything with the xlWB object."

 What error go you get?  91?

2. Change your code to:

  On Error Resume Next
  Err = 0
  Set xlWB = xlApp.Workbooks.Open(strDoc)

  If Err<>0 then stop

  as a test.

3. Why the sleeps?

SMAHelpDeskIT ManagerAuthor Commented:
fy:  That is not an option.  I'd like to avoid having to stay loggge in.  Can't say that I haven't done what you suggest for other things, though.  Thanks for thinking outside the box.

JD.  How'd you guess!  :-)  Yes error 91.  The stops won't help me when launched as a task because nobody will be there to examine the stop.  And, remember, I don't get the error when run "manually" by me.  I put the sleeps in there to give the system time to process the task.  I have found, in the past, when accessing external apps, sometimes the sleep give the system time to "catch up".  Also, some interal Access commands need Doevents to finish a task or two...  (Displaying information to a form is one notable example).  Also, I have an abort button on the form.  The DoEvents gives the system the ability to process that task.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The stops won't help me when launched as a task because nobody will be there to examine the stop.  >>

 Maybe I mis-understood, but I thought you said that even when logged in, but when running out of task scheduler it failed?

 I was suggesting the stops only as a test...

SMAHelpDeskIT ManagerAuthor Commented:
Yes.  The above steps fail when the app is launched as a scheduled task by Windows Task Schedular.  The whole thing works beautifully when I manually launch the database.

(The database, when opened, pops up a form that says, "You have 30 seconds to close this form or an automated process will begin".  After 30 seconds, if the form is still open, it "launches the vipers".)
SMAHelpDeskIT ManagerAuthor Commented:
Just got the source of confusion.  In addition to what I just stated, when "Run scheduled task" (from the Windows 7 Task Schedular applet), the Set xlWB line fails.

And it should be noted, I selected the option, "Run with the highest privilages".
SMAHelpDeskIT ManagerAuthor Commented:
Sorry folks,  I just found it.  Thanks M$ for giving me ten hours of pay!

This folder needs to be created:  c:\windows\syswow64\config\systemprofile\desktop

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SMAHelpDeskIT ManagerAuthor Commented:
This solution in the last post works.
SMAHelpDeskIT ManagerAuthor Commented:
It is self-explanatory.  And - it works!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.