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(strDo
MySleep 2
Set xlSht = xlWB.Worksheets(1) ' <---- I get the error here
With xlSht
.Columns("A:B").Horizontal
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.
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(strDo
MySleep 2
Set xlSht = xlWB.Worksheets(1) ' <---- I get the error here
With xlSht
.Columns("A:B").Horizontal
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Now, if only the process would succeed!
ASKER
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.
ASKER
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.)
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
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(strDo
If Err<>0 then stop
as a test.
3. Why the sleeps?
Jim.
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(strDo
If Err<>0 then stop
as a test.
3. Why the sleeps?
Jim.
ASKER
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.
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.
<<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...
Jim.
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...
Jim.
ASKER
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".)
(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".)
ASKER
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".
And it should be noted, I selected the option, "Run with the highest privilages".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This solution in the last post works.
ASKER
It is self-explanatory. And - it works!
ASKER
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.
Thanks.
I will report back with the results...