0

I have an Excel VBA macro that updates the Excel file, basically sweeping through the records to identify if certain new employees have had training at various points before they reach 90 days, and if not, blasts out emails to distribution groups as a reminder. I would like to have this macro run automatically once a day so my computer will sweep through the file and launch emails when the specific milestone date arises for each record.

The Excel macro runs perfectly. When I manually run the VBS file, it runs perfectly.

When I try to run (manually or scheduled) from Task Scheduler, the log shows it completes but it doesn't run the macro.

I have tried using both the "Run only when user is logged on" and "Run whether user is logged on or not" and both have the same result. My user account (Bachenheimere) is showing as the one to use for running the task, so I obviously have rights to run the file.

For the Task scheduler, I am using Action: Start a Program

Program Script: "C:\Windows\SysWOW64\cscript.exe"

Add arguments: "C:\Users\Bachenheimere\RunExcelMacroForCoachTracking.vbs"

Task Scheduler Pic1

Task Scheduler Pic2

Again, the Task Scheduler log shows it successfully completes in about 1.5 minutes (when I run manually it completes in about 15-20 seconds). I would prefer not to run the VBS file manually each day.

Here is the VBS file code:

Dim xlApp, xlBook


Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\Users\Bachenheimere\file.txt",2,true)

Set xlApp = CreateObject("Excel.Application")
objFileToWrite.WriteLine(FormatDateTime(Now) & "Create Excel Object")
xlApp.Application.Visible = True
objFileToWrite.WriteLine(FormatDateTime(Now) & "App Visible = True")
xlApp.DisplayAlerts = False
objFileToWrite.WriteLine(FormatDateTime(Now) & "Display ALerts = False")
Set xlBook = xlApp.Workbooks.Open("H:\EmCare\Clinical Services\Provider Coaching\NEOU Tracking\Coach Trackingv3.xlsm", 0, True)
objFileToWrite.WriteLine(FormatDateTime(Now) & "Excel book opened")

xlApp.Run "Sheet1.SendReminderMail"
objFileToWrite.WriteLine(FormatDateTime(Now) & "Macro is run")

xlBook.Save
objFileToWrite.WriteLine(FormatDateTime(Now) & "Macro is saved")


xlBook.Close
objFileToWrite.WriteLine(FormatDateTime(Now) & "Book closed") 

xlApp.Quit
objFileToWrite.WriteLine(FormatDateTime(Now) & "App Quit")

objFileToWrite.Close
Set objFileToWrite = Nothing

set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Your Automated Task successfully ran at " & TimeValue(Now)
WScript.Quit
TylerH
  • 20,799
  • 66
  • 75
  • 101
ENoob
  • 1
  • 2
  • 2
    Nothing we could tell you without seeing your code and having a lot more information about the scheduled task runs. [See here](https://stackoverflow.com/a/41635982/1630171) for some debugging guidelines. – Ansgar Wiechers Jul 11 '19 at 15:37
  • Sorry about that - updated with VBS file code and screen shots from the Task Scheduler. Please let me know if you would like to see the Excel VBA also (rather long), although there are no issues with that executing. – ENoob Jul 11 '19 at 17:15
  • `xlBook.SaveAs ...` should be giving you an error, since you're trying to replace a file that is already opened (might be masked by `DisplayAlerts = False`, though). Use `xlBook.Save` instead. If that doesn't resolve the problem you need to identify the parts of the script that are slow. You can do that by interspersing the statements in your code with debugging statements that write the current timestamp to a file or the eventlog. By calculating the difference between those timestamps you'll find the statement that takes too long and can then devise step further debug that. – Ansgar Wiechers Jul 11 '19 at 19:16
  • Thanks, I used the xlBook.Save and that still doesn't solve the issue. Could you provide some links or code to add for debugging (sorry, very rookie experience with VBS scripting!) – ENoob Jul 11 '19 at 20:04
  • Handling files in general is covered by the [VBScript documentation](https://learn.microsoft.com/en-us/previous-versions/czxefwt8(v%3dvs.85)). You'd open a file at the beginning of your script (make sure it's in a location the user running the scheduled task has write access to), close it at the end of the script, and put log statements like `ts.WriteLine ("XXX " & Now)` after each other statement in your script (make "XXX" a unique marker for each log statement, so you can identify which part of the script the timestamps belong to). – Ansgar Wiechers Jul 11 '19 at 21:03
  • Thank you- that was helpful - I have updated my code to show how I am now writing to a log. The Task Scheduler will write a log up to : xlApp.Run "Sheet1.SendReminderMail" objFileToWrite.WriteLine(FormatDateTime(Now) & "Macro is run") and then not go any further. Seemingly it is unable to run the Macro in the Excel file. Any thoughts? – ENoob Jul 11 '19 at 21:08
  • If the "Macro is run" line shows up in the log, but nothing beyond, that indicates an issue with the *next* statement (i.e. `xlBook.Save`). If "Macro is run" is the first line *not* showing up, then the problem indeed seems to be with the macro, so you need to take a closer look at that code. – Ansgar Wiechers Jul 11 '19 at 21:14
  • The macro portion is not written to the log. If I run the macro manually in Excel or even with the VBS fine it runs perfectly. Why would the use of Task Scheduler interfere with macro execution? – ENoob Jul 11 '19 at 21:47
  • Depends on what the macro is doing. I wouldn't know. – Ansgar Wiechers Jul 11 '19 at 22:11
  • But if the VBS file sucessfully runs the macro, and executing the VBS using cscript from a CMD line also works, why wouldn't the same work using Task Scheduler? – ENoob Jul 12 '19 at 21:31

0 Answers0