none
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.

    • Moved by David Wolters Tuesday, May 24, 2011 4:12 PM Moving to a more appropriate forum (From:Office 2007 Application Compatibility)
    Monday, May 16, 2011 5:06 PM

Answers

  • 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.



    • Marked as answer by Townfield Tuesday, June 07, 2011 5:36 PM
    Tuesday, June 07, 2011 5:36 PM

All replies

  • 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

    Tuesday, May 24, 2011 2:22 AM
  • 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.


    Tuesday, May 24, 2011 9:49 AM
  • 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

    Tuesday, May 24, 2011 4:13 PM
  • 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

    Thursday, May 26, 2011 2:20 AM
  • 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.


    Thursday, May 26, 2011 2:06 PM
  • 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

    Thursday, May 26, 2011 3:30 PM
  • 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.


    Thursday, May 26, 2011 5:26 PM
  • 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

     

    Thursday, May 26, 2011 11:38 PM
  • 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.

    Tuesday, May 31, 2011 7:51 AM
  • 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.

    Tuesday, May 31, 2011 2:44 PM
  • 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.

    Tuesday, May 31, 2011 3:30 PM
  • 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.

    Tuesday, May 31, 2011 7:22 PM
  • 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.




    Wednesday, June 01, 2011 3:48 PM
  • 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

    Thursday, June 02, 2011 4:58 PM
  • 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.

    Friday, June 03, 2011 5:18 PM
  • 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.



    • Marked as answer by Townfield Tuesday, June 07, 2011 5:36 PM
    Tuesday, June 07, 2011 5:36 PM
  • 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

    Monday, October 17, 2011 2:52 PM
  • 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!

    Monday, October 17, 2011 3:12 PM
  • 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?

    Monday, October 17, 2011 4:13 PM
  • 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

    • Proposed as answer by HWarnimont Friday, February 27, 2015 9:48 AM
    Tuesday, October 18, 2011 8:03 AM
  • 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.
    Tuesday, April 17, 2012 4:35 PM
  • Thank you so much, I have lost a lot of time looking for a solution to this problem, and yours worked like a charm.
    Friday, February 27, 2015 9:49 AM