[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VBA: Pivot table; field update

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

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

650 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