none
refresh micro charts from pivot table with VBA

    Question

  • 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

    Monday, May 07, 2012 3:24 PM

Answers

  • I was not able to resolve it no.  For that particular report I removed the micro charts to avoid the issue.

    I have not however tested it on version 11.0.3  of power pivot.

    thanks for the follow up.  :-)

    Monday, November 25, 2013 3:01 PM

All replies

  • btw.. I also tried adding "Doevents", but that did not seem to do anything.

            Sheet3.Calculate
            DoEvents

    Monday, May 07, 2012 3:49 PM
  • hi could you please check here the link for your reference.

    http://www.ozgrid.com/VBA/pivot-table-refresh.htm

    Wednesday, May 09, 2012 3:28 PM
  • Thanks for the info, but in this case the pivot tables themselves are refreshing fine  the issue is that the microcharts that are referencing one of the pivot tables is not updating when the pivot table changes.

    The pivot tables get refreshed when the slicer selection is changed 

    sC.VisibleSlicerItemsList = Array(sI.Name)'select a slicer item

    Wednesday, May 09, 2012 4:06 PM
  • Zero,

    Is this still an issue?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 24, 2013 5:55 AM
  • I was not able to resolve it no.  For that particular report I removed the micro charts to avoid the issue.

    I have not however tested it on version 11.0.3  of power pivot.

    thanks for the follow up.  :-)

    Monday, November 25, 2013 3:01 PM
  • Great. It sounds like you moved on.

    Did you want an official answer?

    Yes, sorry that it wasn't answered back last year. We're closing these open questions to ensure this doesn't happen any more.

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, November 25, 2013 6:42 PM
  • No worries, I'm all set.  If you or some one else happens to know of a trick to make it work or more likely that it is fixed in the most current release then that would be great.  I'm not to worried about it though.

    thanks again for the response.  I do think these forums can be very helpful :-)

    Monday, November 25, 2013 7:08 PM