Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotitems("(Blank)") in foreign language
Is there an xl constant or standard name somewhere that defines whatever the
local version of "(Blank)" is? I sent a vba pivot table to a colleague in Italy and it refused to behave properly. The problem was eventually tracked down to a new pivot item called "(Vuono)". Since this has to go to places like Croatia, Malaysia and Russia, I think I to find a generic method! Same goes for "(All)".... Many thanks, Nick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotitems("(Blank)") in foreign language
Hi Nick,
I've never tried this myself but Steven Roman in his WRITTING EXCEL MACROS from O'Reily press list this: Enum xlPTSelectionMode xlDataandLabel = 0 xlLabelOnly = 1 xlDataOnly = 2 xlOrigin = 3 xlBlanks = 4 xlButton = 15 xlFirstRow = 256 End Enum Roman warns this is Excel 9 only but you might give it a try anyway. His book does have a lot of reference to constants. If you do a lot of international work it might be worth having. Regards, Mike "Nick O" wrote: Is there an xl constant or standard name somewhere that defines whatever the local version of "(Blank)" is? I sent a vba pivot table to a colleague in Italy and it refused to behave properly. The problem was eventually tracked down to a new pivot item called "(Vuono)". Since this has to go to places like Croatia, Malaysia and Russia, I think I to find a generic method! Same goes for "(All)".... Many thanks, Nick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotitems("(Blank)") in foreign language
Mike:
Thanks for the reply! Unfortunately, this addresses slightly different problem in that it's referring to "selection" whereas my problem is regarding the population of the table. Specifically, if my Pivotfield contains data for "Ireland", "UK" and "(Blank)", I want to suppress the "(Blank)" entry. This can be done with, say, Pivotitem("(Blank)").visible=false Thanks for the suggestion anyway - I'll have a look out for the Steve Roman book. Cheers, Nick "MIKE215" wrote: Hi Nick, I've never tried this myself but Steven Roman in his WRITTING EXCEL MACROS from O'Reily press list this: Enum xlPTSelectionMode xlDataandLabel = 0 xlLabelOnly = 1 xlDataOnly = 2 xlOrigin = 3 xlBlanks = 4 xlButton = 15 xlFirstRow = 256 End Enum Roman warns this is Excel 9 only but you might give it a try anyway. His book does have a lot of reference to constants. If you do a lot of international work it might be worth having. Regards, Mike "Nick O" wrote: Is there an xl constant or standard name somewhere that defines whatever the local version of "(Blank)" is? I sent a vba pivot table to a colleague in Italy and it refused to behave properly. The problem was eventually tracked down to a new pivot item called "(Vuono)". Since this has to go to places like Croatia, Malaysia and Russia, I think I to find a generic method! Same goes for "(All)".... Many thanks, Nick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotitems("(Blank)") in foreign language
Nick,
The blank appears because that item doesn't have any records currently stored in the pivot cache. You can remove the 'blank' items in a pivot field using the following code: Dim objPivotField as PivotField Dim objPivotItem as PivotItem Set objPivotField = PivotTables("table_name").PivotFields("field_name" ) For each objPivotItem in objPivotField.PivotItems If objPivotItem .RecordCount = 0 Then objPivotItem .Delete End If Next objPivotItem Set objPivotField = Nothing cheers peterDavey Austin Health Melbourne "Nick O" wrote in message ... Mike: Thanks for the reply! Unfortunately, this addresses slightly different problem in that it's referring to "selection" whereas my problem is regarding the population of the table. Specifically, if my Pivotfield contains data for "Ireland", "UK" and "(Blank)", I want to suppress the "(Blank)" entry. This can be done with, say, Pivotitem("(Blank)").visible=false Thanks for the suggestion anyway - I'll have a look out for the Steve Roman book. Cheers, Nick "MIKE215" wrote: Hi Nick, I've never tried this myself but Steven Roman in his WRITTING EXCEL MACROS from O'Reily press list this: Enum xlPTSelectionMode xlDataandLabel = 0 xlLabelOnly = 1 xlDataOnly = 2 xlOrigin = 3 xlBlanks = 4 xlButton = 15 xlFirstRow = 256 End Enum Roman warns this is Excel 9 only but you might give it a try anyway. His book does have a lot of reference to constants. If you do a lot of international work it might be worth having. Regards, Mike "Nick O" wrote: Is there an xl constant or standard name somewhere that defines whatever the local version of "(Blank)" is? I sent a vba pivot table to a colleague in Italy and it refused to behave properly. The problem was eventually tracked down to a new pivot item called "(Vuono)". Since this has to go to places like Croatia, Malaysia and Russia, I think I to find a generic method! Same goes for "(All)".... Many thanks, Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
What is "CountA" in German language? ZahlA | Excel Discussion (Misc queries) | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
Loop through PivotItems of PageField - including "(All)" | Excel Programming |