2

I wrote the below VB script about a month a go , the purpose of which is to:

  1. Open an Excel file
  2. Refresh All (yes 2x)
  3. Save a working copy of the file.
  4. 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.

Community
  • 1
  • 1
ubiquetous
  • 31
  • 5
  • Is it possible that the delay is too short and the 2nd `RefreshAll` is interfering with the first? Might explain why it works manually? – CLR Jan 24 '17 at 19:47
  • @CLR Possibly. I will try running with just 1 `RefreshAll` and see if I can adjust the delay. if I get 1 `RefreshAll` to complete, then I will try to integrate the second. Thanks. – ubiquetous Jan 24 '17 at 20:15
  • Also, what if the file is set to `RefreshAll` on opening? If this is a recent development, perhaps it was previously disabled and someone has enabled it? I don't know if that would cause an issue or not but might be worth investigating? – CLR Jan 24 '17 at 20:24
  • @CLR Tried running with one instance of `RefreshAll` ... no success. Also extended `WScript.Sleep` from 2 minutes to 10 minutes ... no success. Still fails at line 9. Thanks for the idea though. – ubiquetous Jan 24 '17 at 20:27
  • @CLR I will check and see which data sources are set to refresh via which triggers - upon opening, x second interval, etc. – ubiquetous Jan 24 '17 at 20:29
  • [Maybe related](http://stackoverflow.com/q/2355998/1630171) – Ansgar Wiechers Jan 24 '17 at 23:44

0 Answers0