[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

VBA: Pivot table; field update

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
PerMagnusStrom
Asked:
PerMagnusStrom
1 Solution
 
Rgonzo1971Commented:
Hi,

try to replace

PivotArray = "" 'an empty string

with

PivotArray = Chr(34) ' the " character

Regards
0
 
PerMagnusStromAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now