Cube Values Timing Out When Batch Printing PDFs

[joeybb]

Good morning all,

I'm currently trying to configure a macro that will cycle through each slicer that I have linked to a group of cube formulas and print that info as a PDF, move onto the next slicer and print that data set, etc. all the way through until the entire slicer list has been completed. I wrote the code below which works quite well up for a while, and then the cube values stop updating and I get a wonderful PDF that has ####### instead of the values of the cube formulas.

Has anyone experienced this? I'm guessing it's due to consumption of RAM but was hoping that someone might have some insight or has run into this problem before? I tried putting a delay into the code to see if that would help, but even when I set it to run at a full 60 seconds between loops it's still erring out.

Here is the code I am currently using:

Sub Delay(NumberOfSeconds As Long)Dim Sec As Long
Sec = Timer + NumberOfSeconds


Do While Timer < Sec
DoEvents
Loop


End Sub




Sub SlicerLoop()




Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches("Slicer_1")
Set SL = sC.SlicerCacheLevels(1)


For Each sI In SL.SlicerItems
sC.VisibleSlicerItemsList = Array(sI.Name)
Application.CalculateBeforeSave = True
Call Delay(5)
' Create and save .pdf
Dim pdfName As String, FullName As String
pdfName = Range("X1").Text
FullName = "C:\Users\jwoidami\Desktop\Scorecards\" & pdfName & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
, Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=False




ActiveWorkbook.SlicerCaches("Slicer_1").ClearManualFilter




Next


End Sub

Any insight or ideas would be greatly appreciated! this is really starting to get under my skin!

[Kenneth Hobs]

Welcome to the forum!

Try a loop to check if the filename exists or have it check and compare FileLen() changes at some interval.

[joeybb]

Thanks Kenneth! unfortunately the issue isn't with the length of the saved pdf, it's just the data not refreshing as the code loops through the slicer, so I'm not sure that solution would work unfortunately.

[crispincross]

It makes me so satisfied your vast understanding and wisdom have a new channel on the world. I am come a cross to your site and found it according to my need and demand.

[snb]

As far as I can see you overwrite the PDF each time (unless range("X1") changes it's value after each loop)
There's no need to build any waiting procedure, because the next line will be executed only after finishing the previous one.
This code might suffice:

Sub M_snb() 
    For Each sI In ActiveWorkbook.SlicerCaches("Slicer_1").SlicerCacheLevels(1).SlicerItems 
        .Parent.Parent.VisibleSlicerItemsList = Array(sI.Name)
        ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Users\jwoidami\Desktop\Scorecards\" & activesheet.range("X1").value & ".pdf"
    Next 
End Sub

[joeybb]

Hey snb, you're actually spot on, the range is tied to the slicer, so as the macro goes through each value in the slicer list, that value is populated into X1 so the save macro will save with whatever data is being displayed... Basically the value that's in cell X1 is:

=CUBERANKEDMEMBER("PowerPivot Data",Slicer_1,1)

[crispincross]

Thanks for help ...!