?
Solved

Problem with VBS using refreshall statement.

Posted on 2007-07-24
19
Medium Priority
?
1,386 Views
Last Modified: 2008-01-09
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

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?
0
Comment
Question by:wint100
  • 7
  • 5
  • 5
  • +1
19 Comments
 
LVL 23

Accepted Solution

by:
MalicUK earned 1600 total points
ID: 19554629
Hi wint100,

Not sure what the issue is and why it's changed recently. However you might have more success if you loop through the queries and update each individually. There isn't really any difference between the methods apart from the amount of code. Amended code is:

Dim xlApp, wb, fld, fil, thisdate, ws, qtb

fld = "d:\reports\"
fil = "report.xls"

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(fld & fil)

' Refresh the queries

For Each ws In wb.Worksheets
If ws.QueryTables.Count > 0
For Each qt In ws.QueryTables
qt.Refresh
Next
End If
Next

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
End Sub

Cheers,
MalicUK.
0
 
LVL 1

Author Comment

by:wint100
ID: 19554782
This eems to work but slows down the whole data collection.

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?
0
 
LVL 1

Author Comment

by:wint100
ID: 19554820
IS there any way to refresh all queries sheet by sheet, rather than a query at a time?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:MalicUK
ID: 19556101
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.
0
 
LVL 23

Expert Comment

by:MalicUK
ID: 19556154
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.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19563306
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.
0
 
LVL 1

Author Comment

by:wint100
ID: 19564330
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'"
0
 
LVL 1

Author Comment

by:wint100
ID: 19564351
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?

0
 
LVL 23

Expert Comment

by:MalicUK
ID: 19565229
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.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19569915
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.
0
 
LVL 1

Author Comment

by:wint100
ID: 19572135
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.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19572191
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.
0
 
LVL 43

Expert Comment

by:Rob
ID: 19572436
Yeah check that you haven't got background refresh ticked on the query definition (right click the query table and click properties)
0
 
LVL 43

Expert Comment

by:Rob
ID: 19572438
Background refresh will allow you to keep using excel while it waits for the data to be returned
0
 
LVL 1

Author Comment

by:wint100
ID: 19573092
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.Application")
Set wb = xlApp.Workbooks.Open(fld & fil)

' Refresh the queries

wb.RefreshAll

0
 
LVL 23

Assisted Solution

by:MalicUK
MalicUK earned 1600 total points
ID: 19573893
Hi wint100,

You need to turn off background refresh manually in each query, as I said in an earlier post. This will have the effect of increasing the update time, but I've reached the conclusion that you are not going to get everything here. Either sleeping the code, refreshing each query individually with code, or turning off background refresh for all queries are the options you have.

When refreshing individual queries you can do BackgroundRefresh:=False, however this flag is not available for RefreshAll.

MUK.

0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 400 total points
ID: 19578505
MUK is right.  You may be able to turn off BackGround refresh manually, but I would think that then, even when you do the RefreshAll, it will have the same effect as singularly iterating through each of them anyway.  You're only really option to make it the most efficient, would be to somehow monitor the RefreshAll process.......

Try having a look at this....
http://community.salesforce.com/sforce/board/message?board.id=VB_development&message.id=1605

Regards,

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19578520
Wait, hang on.....I just found this snippet:
'============
ActiveWorkBook.RefreshAll
With Worksheets(1).QueryTables(1)
   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.
0
 
LVL 1

Author Comment

by:wint100
ID: 19615692
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question