access 2007 vba to excel 2007 vba 1

[aldi07]

Hi, I am trying to remove a data field from an excel pivot table, using access vba:
Here is the part of the code concerned:

Select Case NbOfCols
Case 3
.ActiveSheet.PivotTables(MyTabID).PivotFields("Col3").Orientation = xlHidden
If MyCategory = "PM" Then
.ActiveSheet.PivotTables(MyTabID).AddDataField ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col4"), "All Projects In Process", xlSum
Else
.ActiveSheet.PivotTables(MyTabID).AddDataField ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col5"), "All Projects In Process", xlSum
End If
Case 4
.ActiveSheet.PivotTables(MyTabID).PivotFields("Col4").Orientation = xlHidden
If MyCategory = "PM" Then
.ActiveSheet.PivotTables(MyTabID).AddDataField ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col5"), "All Projects In Process", xlSum
Else
.ActiveSheet.PivotTables(MyTabID).AddDataField ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col6"), "All Projects In Process", xlSum
End If
Case 6
.ActiveSheet.PivotTables(MyTabID).PivotFields("Col6").Orientation = xlHidden
If MyCategory = "PM" Then
.ActiveSheet.PivotTables(MyTabID).AddDataField ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col7"), "All Projects In Process", xlSum
Else
.ActiveSheet.PivotTables(MyTabID).AddDataField ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col8"), "All Projects In Process", xlSum
End If
End Select

As soon as it hits the first line with the "xlHidden", it does not perform it, and jumps to the "End Select" statement. Any clue?
Thank you.

 

[PHV]

Anyway:
AddDataField [highlight].[/highlight]ActiveSheet.PivotTables( _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aldi07]

Hi PHV,
You are quite right. I don't know how I missed that point. It works fine now.
Thank you!

 

[aldi07]

Actually, I thought it worked because it did not produce an error. But unfortunately, it just went over the statement without processing it: The column was not hidden.
Any other clues?
Thank you.

 

[Andrzejek]

As a side note, wouldn’t be easier to write the Select statement this way?
[tt]
.ActiveSheet.PivotTables(MyTabID).PivotFields("Col"[blue] & NbOfCols[/blue]).Orientation = xlHidden

If MyCategory = "PM" Then
[blue]intX[/blue] = NbOfCols + 1
Else
[blue]intX[/blue] = NbOfCols + 2
End If

.ActiveSheet.PivotTables(MyTabID).AddDataField.ActiveSheet.PivotTables( _
MyTabID).PivotFields("Col" & [blue]intX[/blue]), "All Projects In Process", xlSum
[/tt]
(Unless I missed something….)


Have fun.

---- Andy