Refresh All vba from another Workbook using Task Scheduler

[Plukey]

I'm currently using Task Scheduler to open a workbook. That workbook has a Workbook Open vba that opens the Target Workbook and refresh all. I don't use Task Scheduler to open the target workbook because its being opened through out the day for viewing.

It works, I'm just wondering if anyone uses a different method that's better than the one I'm using.

Sub Workbook_Open()
With Workbooks.Open("C:Path\.xlsm")
        .RefreshAll
        Application.Wait Now + TimeValue("00:00:02")
        .Save
        .Close True
    End With
Workbooks("Workbook.xlsm").Save
MsgBox "Update complete"
End Sub

 

[Dan_W]

It wouldn't be very different from what you're currently doing, but you could always convert the above into a VBScript file and have Task Scheduler execute it direct. VBScripts are effectively text files that contain a form of Visual Basic that can be executed like any other executable. So if you were to type

MsgBox "Hello"

into a text file and save that file to have the extension VBS, you could run it simply by double clicking on the file in File Explorer.

I haven't tested it, but the following in a VBScript file should reproduce what you have above:

Dim XLApp, WB, Filename
Set XLApp = CreateObject("Excel.Application")
Filename = "C:\PATHTOYOURFILE\File.xlsm"
Set WB = XlApp.Workbooks.Open(Filename)
WB.RefreshAll
XLApp.Wait Now + TimeValue("00:00:02")
WB.Save
WB.Close True
XLApp.Quit
MsgBox "Update complete"

What it doesn't do is save Workbooks.xlsm, because I assume that's the workbook you've opened in order to run the code?

 

[Plukey]

It wouldn't be very different from what you're currently doing, but you could always convert the above into a VBScript file and have Task Scheduler execute it direct. VBScripts are effectively text files that contain a form of Visual Basic that can be executed like any other executable. So if you were to type

MsgBox "Hello"

into a text file and save that file to have the extension VBS, you could run it simply by double clicking on the file in File Explorer.

I haven't tested it, but the following in a VBScript file should reproduce what you have above:

Dim XLApp, WB, Filename
Set XLApp = CreateObject("Excel.Application")
Filename = "C:\PATHTOYOURFILE\File.xlsm"
Set WB = XlApp.Workbooks.Open(Filename)
WB.RefreshAll
XLApp.Wait Now + TimeValue("00:00:02")
WB.Save
WB.Close True
XLApp.Quit
MsgBox "Update complete"

What it doesn't do is save Workbooks.xlsm, because I assume that's the workbook you've opened in order to run the code?

Nice! I was just reading up on VBScript, I'll give it go. Thanks for the idea!