-1

I have a powerpivot table and one slicer with several values. I have a macro that loops over my slicer and makes a copy of the data and paste the values in another excel

After any data update when I change the slicer Excel takes some time to query or recalculate values and in the cells appear "#######" during some seconds until the the new data are loaded.

This is a problem for my macro because it is copying this characters instead of the values. I have try two approaches to give more time to the loading process:

  • Create a Wait of 5 seconds that is more than enough before copy-pasting the data
  • Put a Msgbox to stop the copy pasting.

None of them work. Both of them seems to interrupt the loading process that I though will run in the background.

Is there any event in vba that notifies me when the data are loaded?

Community
  • 1
  • 1
user2082695
  • 250
  • 3
  • 14
  • 1
    I suspect you want to add `Application.CalculateUntilAsyncQueriesDone` before you copy the table. – Rory Oct 13 '14 at 10:24
  • If you should share parts of your code to look at... – Jur Pertin Oct 13 '14 at 10:44
  • That's it Rory, thanks – user2082695 Oct 13 '14 at 14:52
  • You should also add Application.Calculation = xlCalculationAutomatic before your Async command, otherwise it seems to hang for ever if it's on manual calculation. You can set it back to xlCalculationManual afterwards as a neat 3 lines of code. – baldmosher Sep 08 '16 at 14:37
  • An additional word of warning: I think if you use CUBESET, then Application.CalculateUntilAsyncQueriesDone seems to hang for ever. It may be recalculating the entire model??? You can't escape from this command either -- Excel stops responding completely and you have to kill it. So use with care. – baldmosher Oct 31 '16 at 17:34

1 Answers1

0

For those who can't use CalculateUntilAsyncQueriesDone because it hangs indefinitely, I devised a solution for this. It's blunt but it works.

https://stackoverflow.com/a/40792389/1540567

Community
  • 1
  • 1
baldmosher
  • 114
  • 9