I wrote the below VB script about a month a go , the purpose of which is to:
- Open an Excel file
- Refresh All (yes 2x)
- Save a working copy of the file.
- If it is Friday, then Save an archived version of the file using the date as part of the naming convention.
It has been running fine every day since then via the Task Scheduler. However, today I received an error - 800A03EC - that refers to line 9 the RefreshAll method.
Option Explicit
Dim xlApp, xlBook, xlSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("G:\MyFolder\MyFile.xlsx")
Set xlSheet = xlBook.worksheets.item(1)
xlBook.RefreshAll
WScript.Sleep(2*60*1000)
xlBook.RefreshAll
WScript.Sleep(2*60*1000)
xlBook.Save
If Weekday(Date()) = 6 Then
xlBook.SaveAs "G:\MyFolder\" & Year(Date()) & "\" & "MyFile - " & DatePart("m", Now) & DatePart("d", Now) & DatePart("yyyy", Now) & ".xlsx"
End If
xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Diagnostic Attempts:
- Excel File does open without error
- Once open I can RefreshAll manually in the Excel file
- Excel file name has not changed.
- If I comment out the RefreshAll lines in the script it runs without error.
- I have one other VB Script that runs via the Task Scheduler.
- It has completed successfully.
- It doesnot use the RefreshAll method. It does use a macro to update data.
At this point any suggestions would be helpful. Thanks.