VBScript ends before saving and closing Excel workbook

Question

I have a very basic VBScript that I want to be able to call from Task Scheduler to open an Excel document, refresh all and then save and close it. I've been playing various bits of code I've found online (as I'm very new to this) and I can get it to open and refresh all, but the VBScript closes before it will save and/or close the workbook.

I have tried adding 'WScript.Sleep' (with various amounts of time) as well as DoEvents to allow Excel to finish, but the script still closes before saving and closing workbook.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = 
objExcel.Workbooks.Open("\\llfs01\ReportHub(Shoals)\Sales\Daily 
Reports\F&I Daily Reports\CIT Report.xlsm")

objExcel.Application.Visible = True
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objWorkbook.WorkSheets(1).Activate
objWorkbook.RefreshAll 'Refresh everything
objWorkbook.DoEvents 'Let Excel finish
WScript.Sleep 20000
objExcel.ActiveWorkbook.Save
DoEvents
objExcel.ActiveWorkbook.Close

I need the code to open the workbook (while my PC is locked and unattended), refresh all data within the workbook, save it and close the workbook.

Remove both DoEvents and sleep as they are unnecessary.. — catcat
Excel completes each of your commands before doing the next. DoEvents is dangerous and should never be used to randomly solve a problem. See my answer here stackoverflow.com/questions/54176056/… — catcat
Thank you. I have read the post you shared and I understand that DoEvents can cause issues by allowing things to run unchecked and the script never be able to finish. — Chris James

Tate Garringer Tate Garringer · Accepted Answer · 2019-01-22T19:53:46

You could set a variable = objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") and then loop Do Events until objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") > variable

Dim s

s = objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

objExcel.ActiveWorkbook.Save
Do Until objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") > s
    DoEvents
Loop
objExcel.ActiveWorkbook.Close

EDIT:

Added to body of original code per OP's comment

Dim s
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("\\llfs01\ReportHub(Shoals)\Sales\Daily 
Reports\F&I Daily Reports\CIT Report.xlsm")

s = objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

objExcel.Application.Visible = True
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objWorkbook.WorkSheets(1).Activate
objWorkbook.RefreshAll 'Refresh everything

objExcel.ActiveWorkbook.Save
Do Until objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") > s
    DoEvents
Loop
objExcel.ActiveWorkbook.Close

VBScript ends before saving and closing Excel workbook

1 Answers