Re: Synchronizing VBA Script

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Just to be clear then:

UDF - retrieves data.

Your VBA code - updates Pivot Table.

What I don't understand is this - what kicks off your pivot table update? I assume it is the VBA code that you have written that performs this task (refresh) - if so *how do you trigger it*?. Are you using events? This would appear to me the crux of the issue.

I'm assuming (again) that you are using the Worksheet_Calculate event to trap the updating of your UDF cells. If this is the case I would propose something like the following (placed in the respective worksheet module) would do the trick:

Private Sub Worksheet_Calculate()
Dim p As PivotTable

   Application.EnableEvents = False
   Set p = Me.PivotTables(1)
   p.RefreshTable
   Set p = Nothing
   Application.EnableEvents = True
End Sub

[Note - without the disabling events the above will fire once for every cell, so they're pretty crucial here.]

This works on the example I set up for myself. I am understanding correctly and if so.... does this work for you?

G



Venu wrote: 
Program execution flow is almost same as described by you.

- User updates A1 manually.
- A2,B2,C2 etc get updated automatically (there are around 5000 cells); this is an asynchronous operation.
- Hence the code execution continues and updates pivot tables whose inputs are A2,B2, C2 etc. This happens before A2,B2 etc are updated by the asynchronous UDF.

We tried including a msgbox statement just before the pivot table update statement. Sometimes the OK button of the msgbox statement gets activated only when the data retrieval is completed and the program works perfectly; but this does not happen always.

Regards,

Venu

"Gareth" wrote:


OK - I think we're getting there... So, the AddIn provides a UDF which (since it's a UDF) can only update its container cell.

My next question would be therefore, what is triggering the code to refresh your PivotTable? Why is this firing before your data retrieval is complete? Are you using events?

Without the answers to the above questions, I would guess you're using an event to fire when any cell containing this UDF updates. However, if you have multiple cells using the UDF and want to wait for all the cells to be updated then this could be a headache e.g.

Cells with Input Data
 A1   B1   C1   D1

Cells with the UDF (with above cells as arguments)
 A2   B2   C2   D3
 A3   B3   C3   D3
 A4   B4   C4   D4

A1 is manually updated:
  Cell A2 updates - Pivot table refreshes
  Cell A3 updates - Pivot table refreshes
  Cell A4 updates - Pivot table refreshes
etc.

What you want is for ALL of the cells to be updated and THEN for the refresh to run. Have I understood the problem properly now?

Venu wrote:

1.  Add-in was supplied by the vendor and we don't have access to the code.

2. Data is being retrived from a database application which is running on MS SQL server. The add-in function is used in Excel cells as formula {=fn(input1, input2,..) }
which automatically recalculates when input1, input2 etc. change.

3. The code is refreshing a few pivot tables to properly arrange the data retrieved from the database. Hence the code should run only after the retrieval is completed.

Regards,

Venu

"Gareth" wrote:



Umm.... without knowing more about the retrieval process it's difficult to say.

- Is this an AddIn you wrote (and therefore have access to the code)?

- Where is data being retrieved from and how?

- What code is running that need to wait? Is it some code that calls the retrieval process? An event that runs?

G

Venu wrote:


I tried the logic you had suggested; but the control comes out of the do loop before the data retrieval is completed; Excel calculation may be completing before the data transfer is over. Could you please provide any other option ?

Venu

"Gareth" wrote:




Providing Excel doesn't finish calculating until the data transfer has finished... you could do something like:

 'Wait until sheet finishes calculating
 do
    Doevents
 loop until Application.CalculationState  <> xlDone

That should wait until it's finished calulating. Of course, you need to ensure it starts calculating in the first place. Although it seems to be working now you might like to throw in an

Application.calculation = xlCalculationAutomatic

or at least check it is in your code before launching the import

or maybe check the current settting, set it to automatic and then change back if necessary.

I haven't tried all this but I hope it works.

Gareth

Venu wrote:



It is not possible to know whether data retrieval has been completed; data just gets populated in various cells. Excel status bar shows the progress such as "Calculating Cells : 75%".

"Gareth" wrote:





Do you have a way of knowing when the data retrieval has been completed? If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

Venu wrote:




How can I force the VBA script in Excel to wait till an Excel add-in function completes retrieving data asynchronously from an external data source. I tried Msgbox statement; but the OK button becomes active before the data retrieval is completed. 


.

Relevant Pages

  • Re: Synchronizing VBA Script
    ... This causes UDF cells A2,B2 etc to retrieve new values from the database ... code continues execution to refresh the pivot tables. ...
    (microsoft.public.excel.programming)
  • Major linking problems
    ... Each CM's worksheet contains the above ... SUMMARY spreadsheet I created a Pivot table to pull the info from my DB. ... the worksheet "named" refresh. ... I can link CM01 info to each of the cells in these scheme, ...
    (microsoft.public.excel.worksheet.functions)
  • Major linking problem
    ... Each CM's worksheet contains the above ... SUMMARY spreadsheet I created a Pivot table to pull the info from my DB. ... the worksheet "named" refresh. ... I can link CM01 info to each of the cells in these scheme, ...
    (microsoft.public.excel.programming)
  • using the RefreshTable method
    ... If I refresh the data in a pivot table, ... "Do you want to replace the contents of the destination cells?". ...
    (microsoft.public.excel.programming)
  • Re: Conditionally Hide Rows
    ... Then you can show cells not equal to 0. ... Dave Peterson ... but I've tried that and it does not refresh when ... I'm looking for something that updates when I ...
    (microsoft.public.excel.misc)