Task Scheduler intermitent using .vbs file

Question

Hi All

I have a couple of .vbs files that I have created one is to run a macro is MS Access and the other is to refresh all data connections and pivot tables in MS Excel.

My problem is these .vbs files will run intermittently even though it is set to run every day at separate times and I never turn my PC off.

The file history says its ran and completed, successfully however in most cases the excel spreadsheet has not updated the connections or pivots. In most cases the MS Access macro has run and created the table which the excel spreadsheet has the data connection to this table.

Anyone got a solution should I change the .vbs to .bat files instead ?

Cheers in advance

coxy63

Answer 1

What I want you to do is be a pessimist for a while, and think of each line: what if it fails? How can I detect that? How can I log where that failure happened?

Another way to say it: practice defensive programming.

Answer 2

thanks Tom

I am a newbie at .vbs / .bat

the MS Access .vbs works all the time its a simple code

Dim oAccess

Dim oWorkspace

Set oAccess = CreateObject("Access.Application")

oAccess.OpenCurrentDatabase "Z:\FolderName\MSAccessDBName.accdb"

oAccess.DoCmd.RunMacro "macroName"

oAccess.CloseCurrentDatabase

oAccess.Quit

this is the excel one which is intermittently working eg: this morning failed to work yesterday worked ?

RunMacro

Sub RunMacro()

  dim xl,path,xlBook

  Set xl = CreateObject("Excel.application")

  Set xlBook = xl.Workbooks.Open (path & "z:\FileName\workbookname.xlsm", 0, False)

  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")

 xl.Application.Visible = True

  xl.DisplayAlerts = False

  xl.Activeworkbook.RefreshAll  

  xl.ActiveWorkbook.Save

  xl.Activeworkbook.RefreshAll

  xl.ActiveWorkbook.Save

  xl.ActiveWindow.close

  Set xlBook = Nothing

  xl.Quit

  Set xl = Nothing

End Sub

Answer 3

If this happened to me, I would add more error handling, and more logging of the steps that were (not) taken. Hopefully that would lead me closer to the solution.

I seriously doubt the scripting technology has anything to do with it, but some tech (e.g. PowerShell) allow for more and easier error handling and logging than others (bat).