Event "BeforeRefresh" for PivotTable
Good day!
The goal is to calculate the duration of updating process*of PivotTable. PivotTable can be updated by the button (Refresh) or by simple manipulation (tuning filters, for example). To get the time during which the PivotTable is being updated, you need to know the start time and the end time of the update. When updating is finished the event Worksheet_PivotTableUpdate is raised. But for beginning of the update there is no such events, like BeforeRefresh for QueryTable.
Question: how to define the start of the updating process of*PivotTable?
Excel 2007/2010, pivottable based on external sources (PivotCache.OLEDBConnection).
I would be grateful for any idea! And even for the words - "you can not do that!"
Filters are different from Refresh, but there isn't an event for that (unless it's based on a querytable in your workbook). The only option I can think of would be to hook the Refresh button. Why do you need to do this?
I have a lot of Excel files with PivotTables inside. And i want to know how much time users spend for refresh each table. In fact any change of PivotTable structure (filtering,sorting,changing fields position) causing PivotCache refresh.
Using filters should not cause the cache to refresh.
In case of MSOLAP connection, Excel throw MDX query to SSAS on any change of filters. In status bar we will see 'running OLAP query (press Esc to cancel)'
Ah, well you didn't mention OLAP before.
Either way, the answer is the same.
You are mistaken. When you change some filter:
1. Excel throw MDX query to SSAS (SQL Profiler, in StatusBar you can see 'running OLAP query (press Esc to cancel)')
2. PivotCache.RefreshDate changes to current time
Test it
You misunderstood me. I meant that the answer to your problem is still no.
Oh, got it... sorry!
Just noticed...
Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!
Read this to understand why we ask you to do this
Thanks for the clarification!
Maybe, is possible to catch any action on PivotTable? For example left mouse click on PivotTable? This would solve the problem.
You've only really got the worksheet events - Change, SelectionChange but I think the change event would be too late.
Yeah... The standard solution is not suitable...
I think you're out of luck then, I'm afraid.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks