Problem with VBS using refreshall statement.
Posted on 2007-07-24
I have the following VBS that runs each day to collect ODBC data from our system.
Dim xlApp, wb, fld, fil, thisdate
fld = "d:\reports\"
fil = "report.xls"
Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
thisdate = Date
fil = fld & "Report (" & DatePart("d", thisdate) & Right("00" & DatePart("m", thisdate), 2) & _
DatePart("yyyy", thisdate) & ").xls"
.DisplayAlerts = False
.DisplayAlerts = True
Set xlApp = Nothing
Set wb = Nothing
The XLS files should open and refresh all ODBC data on the workbook but for some reason it is failing to do so. However, if I open the workbook manually and click the refreshall button, all data is then retrieved. No error are flagged and I can see that the data is requested from our ODBC server, but the fields are not updated on the SAVED workbook.
Can anyone help?