none
Setting Excel slicers in Access VBA RRS feed

  • Question

  • Hi all.

    I'm trying to set slicers on an Excel Pivot Table that I created from Access.  The data source is non-OLAP.  However, unlike most Excel methods, it seems like slicers don't follow identical syntax in Access!  So far I have learned that I must fully qualify the object model, even though I'm operating in a WITH block that should have handled this.  Not qualifying the object model results in "Application or object-defined error".  However, even with the below, I'm getting "Object doesn't support this property or method."  Can anybody help? 

    objExcelApp.Application.objWorkbook.SlicerCaches _
    .Add2(objExcelApp.Application.objWorkbook.objWorksheet _
    .PivotTables("PivotTable1"), "FieldName").Slicers.Add _
    objExcelApp
    .objWorkbook.objWorksheet, , "FieldName", "FieldName"
    Friday, July 24, 2015 12:51 PM

Answers

  • Hi Kevin_Whalen,

    There is nothing difference using Excel object reference in Excel with Access. To troubleshoot this issue, I suggest that you separate the code to multiple line and debug the code one by one to see which line caused this issue.

    You can check the parameters you were passing whether it is valid and the method/properties of the subject that you were calling. And base on the code, you were passing 'FieldName' to 'Level ' parameter in Slicers.Add Method.

    Here is a sample code to create an slicer in Excel for your reference:

    Set slicercache1 = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), "Name")
    Set slicerName = slicercache1.Slicers.Add(ActiveSheet, Name:="Name", Caption:="Name")

    You also could record macro to get the similar code for the reference.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 27, 2015 3:27 AM
    Moderator

All replies

  • You might get "better" answers in the excel forum


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, July 24, 2015 3:23 PM
  • First you use

      objExcelApp.Application.objWorkbook

    then you use

      objExcelApp.objWorkbook

    It is unlikely that both are correct.

    You can shorten this to something like this:

    Dim oWB As Object
    Set oWB = objExcelApp.Application.objWorkbook
    oWB.SlicerCaches.Add2 (oWB.objWorksheet.PivotTables("PivotTable1"), "FieldName").Slicers.Add oWB.objWorksheet, , "FieldName", "FieldName"

    Matthias Kläy, Kläy Computing AG

    Friday, July 24, 2015 11:17 PM
  • Hi Kevin_Whalen,

    There is nothing difference using Excel object reference in Excel with Access. To troubleshoot this issue, I suggest that you separate the code to multiple line and debug the code one by one to see which line caused this issue.

    You can check the parameters you were passing whether it is valid and the method/properties of the subject that you were calling. And base on the code, you were passing 'FieldName' to 'Level ' parameter in Slicers.Add Method.

    Here is a sample code to create an slicer in Excel for your reference:

    Set slicercache1 = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), "Name")
    Set slicerName = slicercache1.Slicers.Add(ActiveSheet, Name:="Name", Caption:="Name")

    You also could record macro to get the similar code for the reference.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 27, 2015 3:27 AM
    Moderator