0

I want to "flatten" a pivot table via the ConvertToFormulas function and copy the result into the clipboard.

My code looks something like this:

ActiveSheet.PivotTables(1).ConvertToFormulas True
Range("A1").Select
ActiveCell.CurrentRegion.Select
Application.CutCopyMode = False
Selection.Copy

My problem is that not all cells are fully loaded from the datasource (they show #GETTING_DATA) when my Selection.Copy executes and I am left with cells in the clipboard that contain the text "#GETTING_DATA".

I would like to find a way to wait until all the cells are loaded or at least to wait 10s until (hopefully) all cells are loaded.

I have tried to add 

Application.Wait(Now + TimeValue("0:00:10"))

or

Sleep(10000)

before Copy but this seams to block the thread on which the loading of the cells is done and I am left with with the same result after 10s.

Community
  • 1
  • 1
Andris
  • 1,948
  • 4
  • 13
  • 23

1 Answers1

1

Didn't build pivot table to test the result but see if below code will helps. The idea is to keep looping through the selected cells, until all cells are integer then we copy it:

Sub test()
Dim pcced As Boolean
pcced = False
ActiveCell.CurrentRegion.Select

Do While pcced = False
pcced = chkv(Selection)
Loop

Application.CutCopyMode = False
Selection.Copy    
End Sub


Function chkv(s As Variant) As Boolean
Dim rslt As Boolean
For Each cell In s
If IsNumeric(cell) Then
    rslt = True
Else:
    rslt = False
    Exit For
End If
Next
chkv = rslt
End Function
Alex
  • 1,632
  • 1
  • 12
  • 28
  • Neat idea. I have adapted your code to my needs and my macro is now much more reliable. I have edited my modified code into your answer in case you want to update it. The most significant issue I had with your code is that it does not take into account that row and column labels in a pivot table are not numbers. – Andris Mar 14 '14 at 07:10
  • After a more careful examination this solution does not work. :( Excel VBA is [single-threaded](http://stackoverflow.com/questions/5721564/multi-threading-in-vba). The checking of the cells keeps this thread busy and the deferred update of the `CUBE*` formulas does not happen. If I include a call to `DoEvents` between iterations the Excel GUI stays responsive but the formulas still do not update. – Andris Mar 14 '14 at 10:14
  • @Andris Right I didn't think through on the single-threaded issue, where it results in the same as sleep() or wait(). I will try update the answer as soon as I've one (that's working) =) – Alex Mar 14 '14 at 14:23
  • Meanwhile, I know a way that can be attempt but I don't have time to test it today - do a **batch file** to call the converttoformula function in excel, then use a [Shellandwait](http://www.vbforums.com/showthread.php?505172-RESOLVED-ShellandWait) function to call this batch file. This way it will wait till the convert is done and proceed to the next step – Alex Mar 14 '14 at 14:44
  • @Andris Hi I wonder have you found another way to solve the problem? I was busy these 2 days and couldn't follow up – Alex Mar 18 '14 at 14:05