?
Solved

VBA: Pivot table; field update

Posted on 2014-01-30
4
Medium Priority
?
711 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 52

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 49

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

801 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