Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
Unable to set NumberFormat property of the PivotField class | Excel Programming | |||
Unable to set the NumberFormat Property of the Range Class - ERROR | Excel Programming | |||
Unable to Set LeftFooter Property Class PageSetup (ASP) | Excel Programming | |||
Unable to set Bold property of the Font Class | Excel Programming |