Share via

Check if a Data Field is already in a Pivot Table

Anonymous
2018-08-28T05:34:58+00:00

Hi folks, 

It seems like I am missing something in my code even though it works great, but somehow it adds a data field every time it runs. Is there a way to not add a Data Field if it already exists?

Here is my code:

Sub Import()

Dim ws As Worksheet, pvtCache As PivotCache

Dim wsp As Worksheet

Dim pf As PivotField, pt As PivotTable

Set wsp = Sheets("Pivot")

Set pt = wsp.PivotTables("PivotTable")

ActiveWorkbook.RefreshAll

Sheets("TP_Coois").Cells.Replace ",", "", xlPart

Set pf = pt.PivotFields("Sales")

If pf.Orientation = xlDataField = True Then              '***I believe here is something wrong***

pt.PivotCache.Refresh

Else

With pf

.Orientation = xlDataField

.Function = xlSum

End With

pt.PivotCache.Refresh

End If

End Sub

Any help is appreciated

Nick

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2018-08-29T06:09:01+00:00

Nick,

blnResult = False is not really needed because blnResult is always false until it is set True, but here used for completeness.

Youre basefield is Sales and the pivotfield made by youre code has the caption "Sum of Sales".

Then every time 'Set pf = pt.PivotFields("Sales")' makes a new Pivotfield and that is still Hidden, so pf.Orientation is zero (xlHidden).

(and Hans said it already:  pf.Orientation = xlDataField = True has to be pf.Orientation = xlDataField for testing pf.Orientation or (pf.Orientation = xlDataField) = True ).

Perhaps this is the better way to test if a pivotfield is present, you don't need to change the default name:

Sub Import()

    Dim ws As Worksheet, pvtCache As PivotCache

    Dim wsp As Worksheet

    Dim pf As PivotField, pt As PivotTable

    Dim strName As String

    strName = "Sales"

    Set wsp = Sheets("Pivot")

    Set pt = wsp.PivotTables("PivotTable")

    ActiveWorkbook.RefreshAll

    Sheets("TP_Coois").Cells.Replace ",", "", xlPart

    If Not FieldExists(pt, strName) Then

        Set pf = pt.PivotFields(strName)

        With pf

            .Orientation = xlDataField

            .Function = xlSum

        End With

        pt.PivotCache.Refresh

    End If

End Sub

Function FieldExists(pt As PivotTable, strName As String) As Boolean

    Dim fld As PivotField

    Dim blnResult As Boolean

    blnResult = False

    For Each fld In pt.VisibleFields

        If fld.SourceName = strName Then

            blnResult = True

            Exit For

        End If

    Next

    FieldExists = blnResult

End Function

Jan

Was this answer helpful?

5 people found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-08-29T05:27:16+00:00

    Hey buddy thank you very much, right when I was about to give up... you're like a Ninja.

    The code works fine. 

    So, I was trying to break up your Function. As far as I understand with my beginner skills in VBA... It seems you got the name of every visible fields in PT, and if any name is = strName then it should return True... That is a nice trick :).

    What is the purpose of this expression "blnResult = False2" above "For Each..." line?

    But I don't get it either why this expression doesn't return True "If pf.Orientation = xlDataField = True" I mean it is True...

    But whatever ... Now is all good.

    Thank You.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-08-29T05:17:51+00:00

    Thanks Hans,

    I did try that too, but somehow is not working, it still adds the same field when the code runs.

    However, I tried the Function from jgkzdl, and that seems to do the trick.

    On a side note: you have helped me couple weeks ago when I had a little problem.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-08-28T13:12:43+00:00

    Nick,

    Will this do the job for you:

    Sub Import()

        Dim ws As Worksheet, pvtCache As PivotCache

        Dim wsp As Worksheet

        Dim pf As PivotField, pt As PivotTable

        Dim strName As String

        strName = "Summing_Sales"

        Set wsp = Sheets("Pivot")

        Set pt = wsp.PivotTables("PivotTable")

        ActiveWorkbook.RefreshAll

        Sheets("TP_Coois").Cells.Replace ",", "", xlPart

        If Not FieldExists(pt, strName) Then

            Set pf = pt.PivotFields("Sales")

            With pf

                .Orientation = xlDataField

                .Function = xlSum

                .Caption = strName

            End With

            pt.PivotCache.Refresh

        End If

    End Sub

    Function FieldExists(pt As PivotTable, strName As String) As Boolean

        Dim fld As PivotField

        Dim blnResult As Boolean

        blnResult = False

        For Each fld In pt.VisibleFields

            If fld.Orientation = xlDataField Then

                If fld.Name = strName Then

                    blnResult = True

                    Exit For

                End If

            End If

        Next

        FieldExists = blnResult

    End Function

    Jan

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2018-08-28T08:26:19+00:00

    Does it work if you change

        If pf.Orientation = xlDataField = True Then

    to

        If pf.Orientation = xlDataField Then

    Was this answer helpful?

    0 comments