refresh micro charts from pivot table with VBA


  • Hi

    I have some code that loops through the items on my slicer and prints out the related sheets to PDF.  I have a number of pivot tables and pivot charts connected to my slicer.  Everything works perfectly except for one thing... my micro charts.

    I have a set of spark lines on sheet3 that are lined up next to some Current month, YTD and full year values that are on one pivot table.  Their data source is an identical (row) pivot table located outside of the print range that has a 12 month trend in the columns.

    When I click through each of my slicer items everything lines up fine.  Rows for the two matching pivot tables expand and contract together with the micro charts lining up just fine.  But when I run my code the micro charts seem to lag and represent data from a previous item.   I've tried a number of things to get it to work (sheet recalc and a time delay)... but so far nothing seems to work.

    any ideas?

    Here is my code:

    Private Sub cmdPublishAlltoPDF_Click()
    Dim strPDFName  As String
    Dim strFilepath As String
    Dim sC As SlicerCache
    Dim SL As SlicerCacheLevel
    Dim sI As SlicerItem
        'set output location where the files should be saved to
        strFilepath = "K:\mypath\Reporting\"
        'identifies the slicer to loop thru
        Set sC = ActiveWorkbook.SlicerCaches("slicer_Functional_Dept")
        Set SL = sC.SlicerCacheLevels(1)
        For Each sI In SL.SlicerItems
            sC.VisibleSlicerItemsList = Array(sI.Name)'select a slicer item
            strPDFName = sI.Value & ".pdf" 'use item name as the file name
            Sheet3.Calculate  're calc sheet with micro charts
            Application.Wait Now + TimeValue("00:00:05")  'time delay
            Sheets(Array("BvA Analysis", "BvA Details")).Select  'select sheets to print
            'Prints the selected sheets to PDF
            ActiveSheet.ExportAsFixedFormat xlTypePDF, strFilepath & "\" & strPDFName, , True, False, , , False
            'select only sheet with slicer
            Sheets("BvA Analysis").Select
        Next  'move on to the next slicer item
        MsgBox "Process complete.  Files published to " & strFilepath, vbOKOnly, "Done"
    End Sub

    lundi 7 mai 2012 15:24


Toutes les réponses