Problem with VBS using refreshall statement.
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.Applic
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
wb.RefreshAll
thisdate = Date
fil = fld & "Report (" & DatePart("d", thisdate) & Right("00" & DatePart("m", thisdate), 2) & _
DatePart("yyyy", thisdate) & ").xls"
With xlApp
.DisplayAlerts = False
wb.SaveAs fil
.DisplayAlerts = True
wb.Close true
.Quit
End With
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?
Dim xlApp, wb, fld, fil, thisdate
fld = "d:\reports\"
fil = "report.xls"
Set xlApp = CreateObject("Excel.Applic
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
wb.RefreshAll
thisdate = Date
fil = fld & "Report (" & DatePart("d", thisdate) & Right("00" & DatePart("m", thisdate), 2) & _
DatePart("yyyy", thisdate) & ").xls"
With xlApp
.DisplayAlerts = False
wb.SaveAs fil
.DisplayAlerts = True
wb.Close true
.Quit
End With
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IS there any way to refresh all queries sheet by sheet, rather than a query at a time?
Hi,
>his eems to work but slows down the whole data collection.
Hmm, interesting. I wasn't expecting much difference here. Can you give some indication of what the slowdown is (time taken before, time taken now).
>The refreshall used to work fine until I swapped the report to a different machine.
As I said, I'm unsure as to the exact reason why it has stopped working.
>Is there any setting in Excel that prevents the refreshall from executing from and external VBS?
No. You are using automation of the Excel application, which gives you full access to all the members and functions of that application.
>IS there any way to refresh all queries sheet by sheet, rather than a query at a time?
No, you can either use refreshall or loop through the individual queries.
MUK.
>his eems to work but slows down the whole data collection.
Hmm, interesting. I wasn't expecting much difference here. Can you give some indication of what the slowdown is (time taken before, time taken now).
>The refreshall used to work fine until I swapped the report to a different machine.
As I said, I'm unsure as to the exact reason why it has stopped working.
>Is there any setting in Excel that prevents the refreshall from executing from and external VBS?
No. You are using automation of the Excel application, which gives you full access to all the members and functions of that application.
>IS there any way to refresh all queries sheet by sheet, rather than a query at a time?
No, you can either use refreshall or loop through the individual queries.
MUK.
Just since I've noticed this, you can more easily get your file name than:
fld & "Report (" & DatePart("d", thisdate) & Right("00" & DatePart("m", thisdate), 2) & _
DatePart("yyyy", thisdate) & ").xls"
by using:
fld & "Report (" & Format(Now, "dmmyyyy") & ").xls"
Although if you are wanting to always have 2 figures for the day of the month I would suggest using:
fld & "Report (" & Format(Now, "ddmmyyyy") & ").xls"
Cheers,
MalicUK.
fld & "Report (" & DatePart("d", thisdate) & Right("00" & DatePart("m", thisdate), 2) & _
DatePart("yyyy", thisdate) & ").xls"
by using:
fld & "Report (" & Format(Now, "dmmyyyy") & ").xls"
Although if you are wanting to always have 2 figures for the day of the month I would suggest using:
fld & "Report (" & Format(Now, "ddmmyyyy") & ").xls"
Cheers,
MalicUK.
Hi, instead of doing
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
wb.RefreshAll
Can you do
xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
xlApp.Worbooks(fld & fil).RefreshAll
Regards,
Rob.
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
wb.RefreshAll
Can you do
xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
xlApp.Worbooks(fld & fil).RefreshAll
Regards,
Rob.
ASKER
RobSampson, this returns "Subscript out of Range.
MslicUK, the time difference has gone from 6-10s to around 1minute to complete, there are quite a few quries on the workbook and it has to open a new connection for evry query in turn.
Also, the new formatting doesn't seem to work "Type mismatch: 'Format'"
MslicUK, the time difference has gone from 6-10s to around 1minute to complete, there are quite a few quries on the workbook and it has to open a new connection for evry query in turn.
Also, the new formatting doesn't seem to work "Type mismatch: 'Format'"
ASKER
It seems as though the report is saving the file before the refreshall has completed updating all the fields.
Can this be be made to wait somehow?
Can this be be made to wait somehow?
Hi wint100,
Are your queries set to run as background queries? This could be the problem. Select each of your queries and open the data range properties. Now make sure that "Enable Background Refresh" is unchecked for them all.
This should force Excel to wait for them all to complete.
Cheers,
MalicUK.
Are your queries set to run as background queries? This could be the problem. Select each of your queries and open the data range properties. Now make sure that "Enable Background Refresh" is unchecked for them all.
This should force Excel to wait for them all to complete.
Cheers,
MalicUK.
Hi, that may be returning subscript out of range because
fld & fil
doesn't actually return the name of the workbook. It might just be fil, but I can't tell what's in that.
When you do
xlApp.Worbooks(fld & fil).RefreshAll
the bit between the brackets must represent the exact name of the workbook.
You could also try making BackGroundRefresh false in the code, with
If ws.QueryTables.Count > 0
For Each qt In ws.QueryTables
qt.Refresh BackGroundRefresh:=False
Next
End If
But I've read that this may still take some time, because they wait one after the other.
Regards,
Rob.
fld & fil
doesn't actually return the name of the workbook. It might just be fil, but I can't tell what's in that.
When you do
xlApp.Worbooks(fld & fil).RefreshAll
the bit between the brackets must represent the exact name of the workbook.
You could also try making BackGroundRefresh false in the code, with
If ws.QueryTables.Count > 0
For Each qt In ws.QueryTables
qt.Refresh BackGroundRefresh:=False
Next
End If
But I've read that this may still take some time, because they wait one after the other.
Regards,
Rob.
ASKER
It is the refreshall that seems to save the file before the queries are updated, the query by query method works well but takes a while.
I'm not sure what you're asking. From what I've read, the RefreshAll method is asynchronous, meaning that it will continue to do everything else in the macro (including save if that's in there) before the updates have all finished. That would be why the singular Refresh method takes longer.
You could look around to see if you can wait for the RefreshAll to finish, but that seems difficult. You could just put in a Wait with an "estimated" wait time:
Application.Wait (Now + TimeValue("0:00:15")) ' Wait 15 seconds
Regards,
Rob.
You could look around to see if you can wait for the RefreshAll to finish, but that seems difficult. You could just put in a Wait with an "estimated" wait time:
Application.Wait (Now + TimeValue("0:00:15")) ' Wait 15 seconds
Regards,
Rob.
Yeah check that you haven't got background refresh ticked on the query definition (right click the query table and click properties)
Background refresh will allow you to keep using excel while it waits for the data to be returned
ASKER
I've used wbscript.sleep 20000
This lets it wait 20secs before saving the file. This works fine.
Background refresh is ticked so how will I modify the refreshall script to disable this in the following script:
Set xlApp = CreateObject("Excel.Applic
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
wb.RefreshAll
This lets it wait 20secs before saving the file. This works fine.
Background refresh is ticked so how will I modify the refreshall script to disable this in the following script:
Set xlApp = CreateObject("Excel.Applic
Set wb = xlApp.Workbooks.Open(fld & fil)
' Refresh the queries
wb.RefreshAll
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wait, hang on.....I just found this snippet:
'============
ActiveWorkBook.RefreshAll
With Worksheets(1).QueryTables(
Do While .Refreshing = True
Loop
End With
ExistFault.Show ' Not sure what this line is
'============
Note that only seems to monitor the first QueryTable. If you could find out which single query table takes the longest to refresh, that would be your best bet at monitoring the status.
Regards,
Rob.
'============
ActiveWorkBook.RefreshAll
With Worksheets(1).QueryTables(
Do While .Refreshing = True
Loop
End With
ExistFault.Show ' Not sure what this line is
'============
Note that only seems to monitor the first QueryTable. If you could find out which single query table takes the longest to refresh, that would be your best bet at monitoring the status.
Regards,
Rob.
ASKER
Thanks.
I've used the sleep command to force the WB to wait 20seconds to refresha nd this works well.
In time i will manually disable the background refresh for all queries.
Thanks for your help.
I've used the sleep command to force the WB to wait 20seconds to refresha nd this works well.
In time i will manually disable the background refresh for all queries.
Thanks for your help.
ASKER
The refreshall used to work fine until I swapped the report to a different machine.
Is there any setting in Excel that prevents the refreshall from executing from and external VBS?