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

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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