function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
D FarnsD Farns 

Need Excel 2003 to wait for RefreshAll to complete using sfdcExcelAddin

I've got an Excel workbook with 2 imported SFDC reports that the end user needs to refresh at will. A number of functions need to run after the RefreshAll is complete to calculate, format, archive on so on. Based on some helpful postings on this site I've set a reference to the SFDC.xla to call the RefreshAll from within a module. My problem is that while the RefreshAll method is running, control is passed back to the function. I'm looking for a technique to:

Cause Excel to wait for the RefreshAll to complete before moving on
or
A way to test that the RefreshAll is complete
or
specify the order in which the reports refresh. I could add a 3rd bogus report to run last and use the sheetchange event to proceed. The order in which the reports are listed on the hidden worksheet "SalesForceReportData" seems to have no significance.

Any suggested techniques would be greatly appreciated.
I'm pretty much a VB, VBA guy with little to no coding in SOAP/web services so hooking into the SFDC_xla is ideal. I'd love to know what some of the other methods do or what the parameters are. Where we not intended to program with OfficeEdition?

thanks in advance
D Farns
deepblueseadeepbluesea
Hi D Farns,

yeah, this can really bug you. Problem is: the RefreshAll is asynchronous, so there's no way of really catching it.

Maybe you could use a little trick which worked fine for me: create a routine that puts a certain nonsense value (e.g. "I'm waiting...") into an ID cell near the bottom of a sheet's/report's area just before you call RefreshAll (note: there is a named region "salesforce" on every report-derived sheet). Insert a row on top of the region and in the according cell place a function that subtotals all occurences of this phrase in the column. Add conditional formatting to turn the cell's background to bright red as long as the phrase is there, and to green when it's gone (just for additional visibility). Last, on the first sheet build an overall subtotal that sums all occurences on all sheets and is formatted conditionally as described above -> as soon as the last sheet is updated all subtotal cells are zero and so is the overall subtotal on sheet #1. Of course you could handle the event and fire another VB routine as soon as the sum is zero...

Does this make sense?
Cheers,
Erik

Message Edited by deepbluesea on 11-10-2005 12:05 PM
D FarnsD Farns
I think I do. Is the concept to have the cell containing the nonsense value be overwritten with report data during the refresh therefore causing the subtotal for that sheet to go from 1 to 0?

Also, the number of rows returned in subsequent "RefreshAll's" will presumably increase. I guess I could grab the highest row # in the named region to determine where to insert the nonsense value before calling RefreshAll.

Finally, as for handling the event, would you suggest going into a loop (do until) after calling RefreshAll or is there a more efficient/preferred method?

thanks for the quick response

D Farns
bouscalbouscal
Haven't tested this a lot, but it appears to work.

Selection.QueryTable.Refresh BackgroundQuery:=False

Since it's not a background refresh the code waits for the refresh to complete before continuing.

I've also used,

Application.StatusBar = "Working..."
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.StatusBar = "Ready"

to notify the end user when the query is refreshed.
D FarnsD Farns
Setting BackgroundQuery=False seemed to work great.

I didn't set it in code however.

If you right click within the report area on the worksheet containing the imported report and select "Data Range Properties" you're able to set several External Data Range Properties, one of which is "Enable Backgroud Refresh". With that unchecked I was able to call "SFDCExcelAddin.RefreshAll". The code following that method in the sub ran only after both report queries were complete.
StefanStefan
Great solution, thanks!