Excel 2007 accessing PivotItem.Visible gives error if the field item value is a DATE

Question

I have VBA code to manipulate the content of a pivot table - essentially it hides all but the first N items in a pivotfield.  Under Excel 2003 the code works fine, as expected irrespective of the data content of the pivot field item.  Under Excel 2007, if the data content is a date, the VBA code generates one of several error conditions.

pivotItems(i).Visible = True

Generates error 1004 - Unable to set the visible property of the pivot class

 

If pivotItems(i).Visible = True then...

Trying to test the visibility generates error 13 - type mismatch

 

?pivotItems(i).Visible

Trying to determine the visibility of the pivot item in the VBA immediate window, this generates error 2042

 

I have an illustrative spreadsheet which permits the code to process two different pivot tables, one using a field containg dates; the other a field containing text.  Under Excel 2003, the code works on both pivot tables.  Under Excel 2007 it works on the text field table, but fails on the date field table.

I have looked for answers on numerous support forums including answers.microsoft but found not found any answers... only the same or similar unanswered question.

Answer 1

Hi,

I suggest you posting this thread in the VBA forum, as they should have more professional knowledge on this product and you may get effective solution timely. For your convenience, I would like to list the VBA forum link below:

http://social.msdn.microsoft.com/forums/en/isvvba/threads

Answer 2

Jennifer,

Thank you for your helpful guidance - I was not aware of the VBA forum.

As a moderator, is it possible for you to transfer (move) this thread to that forum please, or do I need to start a new thread in that forum?

 

Regards.

Answer 3

Townfield,

I have moved this for you. It can be found here:

http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/01cb61c7-5e68-4a45-aeff-a70c6dbfe00f

Hope this helps,

David

Answer 4

Excel 2007/2010 PivotTable
Use .PivotItem(n).SourceNameStandard
to match formats for PivotItem(n).Visible = True/False
For US only. International issues add more complexity.
http://c718892.r92.cf0.rackcdn.com/11_13_10.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

Answer 5

Herbert,

Thank you for your interesting pointer - though I am far from clear how to exploit it or the linked resource!  I had not considered the complication of UK vs US data transformation issues.  I can see how (in UK date format) 30 APR 2011 (30/04/2011) could cause some difficulty, but why might this have stopped working in Excel 2007 when it was working perfectly in Excel 2003?

Doing some tests on an Item having the (UK) date value (02/04/2011) [thought this to be the worse possible case - potentially ambigious date with no means of resolution as there would be with 13 Apr 2011 - 13/04/2011 cannot become 04\13\2011 $]...

PivotItem(i).Value = 4/2/2011

PivotItem(i).Name = 02/04/2011

PivotItem(i).SourceName = Error 2042

PivotItem(i).SourceNameStandard = 4/2/2011

PivotItem(i).Visible = Error 2042

PivotItem("4/2/2011").Visible = Error 2042

 

Can you please clarify how the use of SourceNameStandard should be applied to the floowing code snipet which is failing with the type mismacth error...

            If .PivotItems(i).Visible Then _
                .PivotItems(i).Visible = False

Thank you.

$PS - Just thought why does the US use m\d\ccyy - just seems illogical!  The only merit I can see is that it might (if zero padded) text sort correctly for the current year.

Answer 6

Excel 2007/2010 PivotTable
See Excel 2007 VBA Programmer's Reference, wrox.com
Chapter "International Issues"
No solutions, just difficult work-arounds.
Might entertain your file uploaded to MediaFire.com

Answer 7

Herbert,

Sorry I'm just not getting this guidance.  I've read chapter 25 of the book - very interesting and informative - but it makes no reference to issues when trying to alter the visibility of a PiovtItem (whose value is a date) in Excel 2007 which works fine under 2003.

To be clear I'm not interested at all in the date value or trying to manipulate it - I am simply trying to work with the position of the item in the PivotField.  If it is higher than the Nth I want it hidden, if less visible, whilst of course ensuring that at all times at least one item remains visible.

Excel 2003 coughs if you try to set an item to its current visibility, hence the need to test the current state and then flip it if is not already in the desired state.  It is testing the visibility that generates the type mismatch error if the value is a date under 2007.  The code works fine under 2003 and if not a date the code works under 2007 too.

Following your suggestion, I have placed the file here - http://www.mediafire.com/file/6cha8liawh3yhc5/Pivot%20Table%20Hide%20Items%20demo.xls

Thank for your assistance.

Answer 8

Excel 2010 PivotTable
Display n PivotItems.
Date format not an issue here and not addressed.
Use 2007 Tables, 2003 Lists, as PT sources.
http://c3017412.r12.cf0.rackcdn.com/05_26_11.xlsm

 

Answer 9

Herbert,

Please forgive me, I must be thick - I just do not get what you are feeding back to me here, consequently I fear that I might be wasting your time...

1. I have no idea what to do with the xml bundle that you've sent me - can you give me a clue please?  Do I (somehow) need to import this into Excel?

2. This is not a matter of having a solution for 2003 and one for 2007 - there is one spreadsheet which will be used by both 2003 and 2007 users - so I do not quite get how the use of PT in one and a table in the other fulfils the requirement.

Again thank you for your time and valiant attempts to help - it is the most positive I have seen on this across several forums, so dp  not misktake my appreciation.

Regards.

Answer 10

Excel 2010 PivotTable, Tables
Excel 2007/2010 files are now zipped xml files.
If your PC does not recognize that, then manually
change the file suffix to xlsm.
Maybe your PC with Excel 2007/2010 can handle this link better:
http://www.mediafire.com/file/1798825x52ugoh7/05_26_11.xlsm

What I recommend is that you rewrite your 2003 file from scratch
using Lists. The macro needs to refer to Lists, not dynamic ranges.
Excel 2007, 2010 will recognize the Lists as Tables.

Answer 11

Herbert,

 

Thank you - some real progress!

I was able to successfully opened the file in Excel 2007 (2003 left things a bit 'bent' - Pivot tables appeared 'not right' and I could not refresh them) and did some tests.

This code still breaks for me!  pvi.Visible = True generates... RTE 1004 - Unable to set the visible property of the PivotItem Class

I suspect that this is due to the other known issue of trying to set the visibility of a PivotItem to is current state, hence my code testing Visibility before attempting to change it.  Such tests still generate Error 13 type mismatch - IF the PivotItem name / value is a date.  (I changed your code to use my other pivot table and it worked as intended).

I will look further at LISTS (not something I have used that much) however at my end on Excel 2007, the conversion of the data source to a list has not altered the failure characteristics.  For clarity, did the example I sent you fail for you as I described?

Regards.

Answer 12

After you converted your 2003 file to Lists and
removed any reference to (blank)s
I would be disposed to look at your uploaded file again.

Answer 13

Hi Herbert,

Thank you for your continued interest; please find a revised illustration of the problem here - http://www.mediafire.com/download.php?zc6ty9ctl1n7yd9

I have made the following changes / additions to the problem illustrator:-

1. Adopted the use of lists for the data source of the pivot table
2. Removed reference to '(blank)' as a reserved item not hidden by the VBA code to avoid the possibility of hiding all items
3. In respect of my original code (MK1), utilised a 'real' item name to be the reserved item
4. Added a new (MK2) macro to alter the number of visible items which

• hides all but one of the VisibleItems collection
• makes the first N items in the collection visible
• checks that there are only N items visible (the position of the reserved item might be greater than N)

The code still executes as expected under 2003, but fails (the same as before) under 2007 when the data type of the PivotItem is date.

The MK1 code fails when testing the current visibility of the Item before trying to set the visibility with:- RTE 1004 Unable to get the PivotItems property of the PivotField Class

The MK2 code fails when trying to set the visibility with:- RTE 1004 Unable to set the Visible property of the PivotItem Class as reported above.  Note - given that MK2 avoids trying hide invisible items or unhide visible items, I no longer believe that this error is due to the attempt to set visibility to current state as seen under Excel 2003. I am deeply confused by this now!

I observe with care the different CLASSES on which these failures are reported even though in each case the operations are on the PivotItem.Visible property - MK1 queries it / MK2 attempts to alter it. 

 

Just in case you have been wondering where this is going, this spreadsheet has no prupose beyond illustrating the Excel 2003 / 2007 problem being encountered elsewhere when manipulating pivot table items whose value are dates.  The requirement is to provide a simple user interface to a complex managing reporting spreadsheet consisting of a significant number of pivot tables and charts - altering one cell causes the VBA code to modify the collection of pivot tables to deliver a revised view of the data.  All works fine for 2003 users of the model, but not 2007 users.  I need one spreadsheet which will work correctly on both 2003 & 2007.

Regards.

Answer 14

Your latest file works just fine in Excel 2007 with a US WRS.
It fails with a UK WRS.
This is an International Issue as expounded in
the Programmers Reference.
http://c3017412.r12.cf0.rackcdn.com/06_01_11.xlsm

Good Luck with your own conversion to 2007/2010
Fini

Answer 15

Your latest file works just fine in Excel 2007 with a US WRS.
It fails with a UK WRS.
This is an International Issue as expounded in the Programmers Reference.

Herbert,

Thank you for your patience - indeed the programmers reference does hint at this problem but not explicitly in the content of PivotItems - the behaviour is bizzar!  I have just altered the date format / locale within Excel in the 2007 version and the code works as expected.

What a regression against 2003.

I now have a focus on what needs to be fixed and can now look at how this might be achieved.  If I find a solution I'll let you know via another reply here.

Regards.

Answer 16

Herbert,

For your information and for others with this problem - I have cracked it and done so simply!

I have found that by explicitly making the NUMBER format of the PivotField a DATE format (rather than GENERAL) then the .PivotItem.Visible = True instruction performs correctly under 2007, even with UK dd/mm/yyy date formats.  However If Not .PivotItems(i).Visible Then still coughs with the type mismatch error.  Changing the DATE format on the PivotField to dd mmm yyy (without also changing locale) makes the code work correctly under 2007.

NB: The option to set the NUMBER format of a Pivot Field appears to be only available if the source area of the Pivot Table is a DATA LIST.  If the data source is simply a cell range, then the NUMBER button on the field setting diaglogue is absent.

It would appear that Excel 2003 correctly coalesces a GENERAL formatted cell containing a UK date to the 'correct date' and thus execute the VBA code as expected, whereas Excel 2007 / 2010 does not with the consequential type mismatch error.  These versions need help by expressly making the PivotField NUMBER format DATE having a picture which is unambigious between months and days.

In short if you have this problem and you can format the PivotField as DATE the code will then work correctly.

More than anything else, the insight you provided that this code works fine under USA WRS was the eye-opener required to go look elsewhere beyond the VBA code for a solution.  For that I am most grateful - THANK YOU!

Regards.

Answer 17

Thankyou Townfield,

But now how do you accomplish changing the pivotfield format? And how does this affect the item if it is "(blank)" ?

I have been trying to resolve the exact same problem for awhile now. I am located in Sweden. Just loving these international issues with Excel right now!!! yippie!

 

        With pf

            .CurrentPage = "(All)"

            .EnableMultiplePageItems = True

            .Orientation = xlPageField

        End With

        For Each pi In pf.PivotItems

            pi.SourceNameStandard

            If pi.Value = "(blank)" Then

                pi.Visible = True

            Else

                If pi.Value >= varDate Then

                    pi.Visible = True

                Else

                    pi.Visible = False

                End If

            End If

        Next pi

Answer 18

IronyAaron,

The subtle point is that if there are "(blank)" items in the pivot table field data, then the NUMBER button (which allows setting of data (date) formats) on the PivotTable Field options is not visible on the diaglogue window.

Setting your pivot table data source to a DATA LIST or the exact range of the data rows elininates "(blanks)" (in my case any way) and thereby I could access the NUMBER... option to set the format on the pivot field settings.  Hope this makes sense!

Answer 19

Unfortunately, that can not resolve my problem. The data filtering requires the "(blank)" and dates from multiple date fields.

I see this as an international issue within how VBA and the pivotcache reads the dates. 

The dates show up in the pivotfield list in their local computer setting format (Sweden), regardless of how the data in a list/table is formated.

When vba reads the fields, it displays the local Excel setting (US). The pivotitems read and can display the value property. But the visible property and others return "pi.visible = <Type Mismatch>". This is apparent because the VBA value in memory is in US format but the pivotitem is in the Swedish format.

The goal is to get either the pivottable to load its cache with the US format, or force Excel VBA to use Swedish formats for dates, from the beginning of the Modul. I have only figured out how to "Read" application settings. I can not change application settings from within VBA to recognize the dates. I have looked through every source I have available and none address changing how VBA addresses different settings when it analyzes data within pivottables.

To make the issue more difficult, actually calculations between the two date formats in VBA do work (ie. date1(US) = date2(Sweden) produces a proper boolean expression based on the values)

Got any ideas on how pivottable caches can load data differently?

Answer 20

Ok, I have come to a conclusion to resolve the problem. There are three options to resolve the issue. However, some fundamentals of excel must be established prior to describing the solution. 

Excel and VBA are two different programs combined into one entity. Thus they parse information prior to using it. Therefore there are fundamental requirements in handling variables. You can either ensure the variable is in the correct format prior to sending to VBA, which may be difficult due to masking. Or you can force an explicit string to VBA, of which you also risk the programs forcing parsing of the variables. (both may lead to bugs, however they must be identified in order to code the problem correctly)

The first question of correct format leads to a problem when utilizing pivot tables. I realized this while working with my data. Explicit formating data in the excel sheet does not affect the data (and Date) format when data is transferred to the pivot cache. Subsequently, dates are converted to the local computer settings in the pivot cache regardless of the input format.

The result is when VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formated string. This therefore causes VBA to fail when recognizing Date variables.

1. The first resolution is to put dummy dates (a base date such as 1/1/01), in all "(blank)" cells. This will allow the user to force the Excel pivot table to recognize a date format. Then one can filter out the dummy variable rather than a "(blank)".
2. The second option is to convert all dates to string variables within Excel prior to transfer to VBA. Then within VBA you must convert the string variable to the proper format prior to use.
3. The third option is to make the user change their regional settings to US. I bring this up because both solution 1 & 2 require changing over 30 formulas within 1000 lines of code for my project. The hours to do that are significant with debugging after and therefore come at a cost.

Recommended Further Reading:

http://www.oaltd.co.uk/excelprogref/ch22/ProgRefCh22.htm

Answer 21

Brilliant! I've been searching for a solution to this for ages; setting the pivot field filters number format to dd-mm-yyyy and not the *dd-mm-yyyy worked for me.

Answer 22

Thank you so much, I have lost a lot of time looking for a solution to this problem, and yours worked like a charm.