Start excel from Task Scheduler was working, now fails

Question

I've been running my excel 2000 vba program with Task Scheduler and cscript and vbscript for about a year now. A few days ago it stopped working. I can run the program manually. Whenever Task Scheduler starts it, the black window displays for about 1-2 seconds then closes.

I've tried commenting out error handling but I always get the same results. I tried showing MsgBoxes but results are still the same.

I found on your forum here, an easier way to do it and I tried that with the same results.

So what do I need to do to debug this?

Here is code I tried from this forum:

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open("C:\My Documents\___Stocksfilter.xls", 0, True)
xlApp.Run "A_Pick"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit

Here is code I've been using:

'Script to start my filter.xls program with named macro
'MsgBox "In RunExcel"

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application") 

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Open the Workbook 
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = "C:\My Documents\___Stocks\filter.xls"

'MsgBox "Opening filter"

on error resume next 
Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)
if err.number <> 0 Then
    ' Error occurred - just close it down.
    MsgBox "open Error occurred"
End If
err.clear
on error goto 0 

'MsgBox "Running macro"

Dim strMacroName
strMacroName = "A_Pick"
on error resume next 
' Run the macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
    ' Error occurred - just close it down.
    MsgBox "run macro Error occurred"
End If
err.clear
on error goto 0 

' Clean up and shut down
Set oWorkBook = Nothing
myExcelWorker.Quit
Set myExcelWorker = Nothing
Set WshShell = Nothing

Brackett

Welcome to Stack Overflow. +1 on a well written first question. Are you running this on a XP or Win7 (not a server)? and when you debug this without myExcelWorker.DisplayAlerts = False does excel give hints as to what is going on? – Huge

It's running on my old Windows 98 SE computer. It showed me no error information at all. But the black window just flashes and I don't know how to capture the error. I comment out the DisplayAlerts=False along with the other UI lines and the results are always exactly the same. – Brackett

Does it fail in the vb script of the Excel macro? Have you verified the paths are correct? – Stratosphere

Well, the paths are correct. Nothing has changed, such as I haven't moved my files. I presume, it is not getting to the Excel but I don't know how to find out for sure. – Brackett

@Brackett - To pinpoint the issue I suggest you dump the code into Excel VBA, and then debug it line for line. All you need to do is add a Sub Test() in VBA then dump the vbs code between the Sub Test() and End Sub then F8 through it. Let us know where it dies - I agree its probably the path. – Jovial

I did as you said. It got to this line: myExcelWorker Run strMacroName. I believe then it started running my program. My program will run for several hours once started. So I don't see any error then in the vbs. Another thing I thought to do is I issued a Run command for cscript and my RunExcel.vbs. Then the black window flashes for about 1 second. – Brackett

@Brackett In that case I would (1) remove the On Error Resume next to see if strMacroName runs. (2) If it does, run strMacroName directly from filter.xls and see if it completes properly or fails. – Jovial

strMacroName is starting the program correctly. (Everything works when run manually). The problem is cscript does not start the RunExcel.vbs. How can I see that error? – Brackett

Answer 1

I'm going to take a wild guess here.

"It works when I run the cscript but not when the Task runs it" makes me think this is a permissions issue. When you setup a Windows Scheduled Task, you should be able to set it to run as a specific user (and also set whether to run if the user is not logged in). I'm not sure about Win 98, but can you find out what the Task properties say? When you run the code yourself, your permissions are used and the scheduled task might be using a user who doesn't have the permissions to run it.

How can this happen if you didn't change anything? The only thing I can think of is that either:

1. The user that the scheduled task uses no longer exists or the permissions have changed
2. The user had a password expiration date and it has recently passed
3. If the scheduled task is using your username, did you save the password in the Task properties and then recently change your password, but did not update the scheduled task's password?

Also, some anti-virus products stop the use of some vbscripts. Did you possibly install anything new right before it stopped working?

To check to see where the error might be coming from (hoping this works since I'm not very familiar with Win98 machines):

1. Find out which user is set to run the scheduled task
2. Start Command Prompt as another user (I think it's Shift+Right-Click --> Run as... [note, I don't know about Windows 98 so I'm crossing my fingers that you can do this])
3. Run the cscript from the command prompt: C:\>cscript <dir>\myscript.extension \I (note, the \I is Interactive mode, which should show errors (see here for more info)

I really hope this helps. I would have put this in a comment, but it's too much text.

Liquidate

Thanks for your suggestions, joseph4tw. I will investigate and let you know. But Win 98 doesn't have Users. Also, TaskScheduler starts it. I believe that cscript is not finding my RunExcel.vbs file which is there. Like I said, I will do some further testing, now that I have gotten this far. – Brackett

@Brackett I see. Have you tried just re-creating the task rather than trying to understand why it doesn't work? – Liquidate

Here is what I found out. I ran wscript instead of cscript, which showed me the error. The error is "Can't find script engine VBScript ..." I'm looking on internet for solution. Someone said Run "regsvr32 VBScript" and that gave the error--RegSvr32 LoadLibrary(VBScript) failed. GetgLastError returns 0x00000485. – Brackett

Answer 2

Solved! First I did a Run command and used wscript. Could also use cscript /I as joseph4tw advised but I didn't know about that.

This showed me the error that it couldn't find the VBScript engine. I looked on internet and vbscript.dll should be in Windows/System file for my 98 computer. It was not there. I found the site www.dll-files.com and downloaded the file. Then I rebooted. Then I did a Run command to install it, like this: Run regsvr32 vbscript.dll. It gave a success message and now it works.

I believe this was caused by malware. I can't get the malware off my 98 computer because I can't run any of the newer antivirus.

Because of my new XP computer, I am working on making disk image and using a linux recovery CD. That linux, I think will run on 98 and I hope I can run the antivirus to clean up that computer.

Thanks for everyone's help because it led me to the solution.

Brackett

Glad to have helped! +1 for posting a detailed solution – Liquidate

Answer 3

I just faced the same problem.

I have made a .bat file where I called the .exe file.

call filelocation/application.exe

finally, I called the .bat file from the task scheduler actions.

It just works fine.

Dwightdwindle