Solved

VBA: Pivot table; field update

Posted on 2014-01-30
4
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39820748
Hi,

try to replace

PivotArray = "" 'an empty string

with

PivotArray = Chr(34) ' the " character

Regards
0
 

Accepted Solution

by:
PerMagnusStrom earned 0 total points
ID: 39832401
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 47

Expert Comment

by:Martin Liss
ID: 39899989
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

756 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