Home Forums Power Pivot VBA for Template Reports

This topic contains 0 replies, has 1 voice, and was last updated by  dircur 6 months, 2 weeks ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #8711

    dircur
    Participant
    • Started: 14
    • Replies: 31
    • Total: 45

    I use a lot of vba.

    Short version of Question:
    How do a get the FormulaName from a slicer object via VBA???

    Long version of Question:
    I took the PPP online university course (it is so awesome PLUG). I have been incorporating so much. I recently hit a small wall while rebuilding one of my core tools.

    I have a workbook that imports a ton of AP data. I have built a template report for vendors it is essentially 10 rows of summary details via cube formulas, followed by a pivot with a broader scope of summarized data.

    The discovery of cube formulas was paramount to me being able to extend my old workbook as I can now import a ton more data (to the model) without writing them to a sheet. The summary report is controlled off to the side (out of view) by a Slicer (“Address Number”).

    Both the pivot and cube formulas reference this lone slicer on the sheet.
    My routine updates the value in the slicer:

    ActiveWorkbook.SlicerCaches(“Slicer_Address_Number”).VisibleSlicerItemsList = _
    Array(“[Vendors].[Address Number].&[” & sVndrNum & “]”)

    Works awesome 🙂

    Then copies the sheet to another… !!!DERP!!!! Looks so great, deceptively wrong info. While the pivot table references the copied Slicer, the cube values don’t. UGG!!!

    No problem I love VBA, I will just update the formula’s to refer to the new slicer, with my stolen and butchered PPP course workbook code. (FYI I paid for the this amazing course, so technically I think my “theft” is expected!!!)

    For Each oSlicerCache In ActiveWorkbook.SlicerCaches  
          For Each oSlicer In oSlicerCache.Slicers
              If oSlicer.Shape.BottomRightCell.Worksheet.Name = ActiveSheet.Name Then
                   sSlicerName = “Slicer_” & Replace(oSlicer.Name, ” “, “_”)
             End If
          Next
    Next
    For Each Cell In Range(“CubeUpdates”)
          sNewFormula = Replace(Cell.Formula, “Slicer_Address_Number”, sSlicerName)
          Cell.Formula = sNewFormula
    Next

    This nearly works like a Charm but man does excel like to push me over the desk.
    The Slicer Property “Name” is not the name you use in formula’s! ARGH!!!
    Not only is it not the name you use in formula’s the object browser isn’t giving me an obvious property to use to get the name to use in formulas.

    The name returned is Address_Number_1 but for formula name should be Address_Number1. I will kludge my way there to strip the space but ARGH!!!

    But if someone knows how to suck out the formula name via the slicer object, I am looking for a hero.

     

    Thanks

    John

     

     

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.