joeybb
12-11-2013, 10:23 AM
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.:banghead:
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!
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.:banghead:
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!