Solved

VBA: Pivot table; field update

Posted on 2014-01-30
4
651 Views
Last Modified: 2014-03-03
I have a pivot table with a filter that needs to be updated through VBA. The filter must contain several values - please see below:

ActiveSheet.PivotTables("Pivottabell1").PivotFields("[Organisasjon].[Ansvarssted kode].[Ansvarssted kode]").VisibleItemsList = Array("[Organisasjon].[Ansvarssted kode].&[7230]", "[Organisasjon].[Ansvarssted kode].&[7231]", "[Organisasjon].[Ansvarssted kode].&[7232]")

The above code works. The department numbers are, however hard coded.

I have written a loop that generates the string inside the Array(...):

    Dim PivotArray as String
    PivotArray = ""
   
    For N = 1 To 99                   ' picks up all relevant department numbers
        If AnsvStedRekkeSeksjon(N) <> "" Then
            PivotArray = PivotArray & "[Organisasjon].[Ansvarssted kode].&[" & AnsvStedRekkeSeksjon(N) & "]" & Chr(34) & Chr(44) & Chr(32) & Chr(34)
        End If
    Next N
   
    PivotArray = Left(PivotArray, Len(PivotArray) - 4)  ' cuts away the last characters

..
After this loop, PivotArray looks like this:

"[Organisasjon].[Ansvarssted kode].&[7230]", "[Organisasjon].[Ansvarssted kode].&[7231]", "[Organisasjon].[Ansvarssted kode].&[7232]"

But still the code below fails:

ActiveSheet.PivotTables("Pivottabell1").PivotFields("[Organisasjon].[Ansvarssted kode].[Ansvarssted kode]").VisibleItemsList = Array(PivotArray)

I suspect there may be a problem with the character ("), and have tried a number of different syntaxes, but without luck..

The error message is: "Application-defined or object-defined error" - and the code line above is in yellow..

Can anyone PLEASE help...
0
Comment
Question by:PerMagnusStrom
4 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

try to replace

PivotArray = "" 'an empty string

with

PivotArray = Chr(34) ' the " character

Regards
0
 

Accepted Solution

by:
PerMagnusStrom earned 0 total points
Comment Utility
I solved the above problem, by using separate entries an array instead of an "array string":

      Select Case NumberOfSections
                Case 1
                    ActiveSheet.PivotTables("01_Resultatrapport").PivotFields("[Organisasjon].[Ansvarssted kode].[Ansvarssted kode]").VisibleItemsList = Array(ASR(1))
                Case 2
                    ActiveSheet.PivotTables("01_Resultatrapport").PivotFields("[Organisasjon].[Ansvarssted kode].[Ansvarssted kode]").VisibleItemsList = Array(ASR(1), ASR(2))
                Case 3
                    ActiveSheet.PivotTables("01_Resultatrapport").PivotFields("[Organisasjon].[Ansvarssted kode].[Ansvarssted kode]").VisibleItemsList = Array(ASR(1), ASR(2), ASR(3))

       End Select

---

However, a new problem arised:
In a few cases, one of the values I am trying to set for the pivot table filter, does not exist in the pivot, causing a "application-defined or object-defined error".

I need a way to verify that a specific value exists in the pivot table before setting the filter to this value.

Your input is very appreciated :)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now