Unable to set NumberFormat property of the PivotField class
 

Hi

Please help. Pivot Tables are driving me insane.

It was all working fine. Then, suddenly, for no reason, my VBA code
doesn't work anymore. The problem is with a pivot dimension that
contains dates. It's called MonthEnd, and I put it across columns in
the pivot-table.

The week before last this worked:

With objPivotTable.PivotFields("MonthEnd")
..Orientation = xlColumnField
..Position = 1
..NumberFormat = "mmm yy"
..Caption = "Month"
End With

Now it doesn't. On the .NumberFormat line, I get an error 1004: Unable
to set the NumberFormat property of the PivotField class. If I break
on this line and try
"?objPivotTable.PivotFields("MonthEnd").NumberForm at" I get the same
error. (the expression binds fine right up to
PivotFields("MonthEnd").whatever - I can see the properties of the
correct PivotField - just not NumberFormat).

Worse, I can't even set the NumberFormat manually for the field - of
course the Excel Format Cells dialog still works, but the Field
Properties dialog for "Month" now just doesn't have a "Number..."
button.

Somehow Excel is taking the date values from the SQL Server datasource
and "half" recognising them as dates - because they're ordered
correctly as dates (e.g. "30/06/2004" comes after "20/01/2003") - but
there's no way to format them.

The server is running in US English. I've tried setting my SQL login
settings to <default, to English, and to British English.

Why did this work last week, but not this week?

BTW, I've tried this on another machine running Office 2000 SP3 (mine
runs SR1a), but this makes no difference.

any ideas most welcome



Seb