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

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

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

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