none
Type Mismatch Error when creating a PivotCache with > 65536 rows in range

    Question

  • Hi, I am getting an exception error "Type mismatch. (Exception from HRESULT:0x80020005 (DISP_E_TYPEMISMATCH)) when trying to create a PivotCache on a range that has more than 65536 rows. For ranges smaller than this it works fine.  The line it fails on is:

    Excel.PivotCache oPivotCache = (Excel.PivotCache)excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); 

     I also used Create method instead of Add method in above syntax but not got any success as mentioned in below screen shot

    Error when creating a PivotCache with > 65536 rows in range" src="https://social.msdn.microsoft.com/Forums/getfile/1008835" /></p><p style=

    I am developing in VS2015 Express Edition and have MS Excel 2010.

    Any thoughts on why this might be happening or how to get around it?  Thanks.


    • Edited by Vaibhav41 Wednesday, March 01, 2017 3:10 AM Image is not visible
    • Moved by Kristin Xie Wednesday, March 01, 2017 5:30 AM
    Wednesday, March 01, 2017 3:07 AM

All replies

  • Hi Vaibhav41,

    Based on your description, your case more related to Excel, I will help move your case to Excel forum for better support.

    Best regards,

    Kristin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 01, 2017 5:30 AM
  • Hi,

    To check if it is a built-in behavior, I suggest you create a pivottable with >65536 rows manually.  Try to record the macro and then run it again, to see if there is any error.

    >>For range smaller than this it works fine.

    According to the exception message, I would suggest you check if the data type is valid.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 02, 2017 10:05 AM
    Moderator
  • I tried to create pivot table with >65536 rows manually (direct in the excel file) and able to pivot the raw data successfully.

    Just for your clarification, I am executing C# code to create a pivot representation of the  raw data in excel file.

    Can you please help me to write macro to create pivot table? but for C# code it works absolutely fine when rows are less than 65536 rows .

    Please help as it is Urgent issue i have to resolve.

     

    Monday, March 06, 2017 9:58 AM
  • Update: 

    As suggested record a macro for the steps doing from C# to create a Pivot table for data representation and it works fine.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table!R1C1:R1048576C9", Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
            :=xlPivotTableVersion14

    Please review the same and help.



    • Edited by Vaibhav41 Tuesday, March 07, 2017 4:50 AM
    Tuesday, March 07, 2017 4:44 AM
  • Hi Vaibhav,

    Do you mean VBA code works with 65536 rows in range and C# did not work?

    If so, could you share us a simple file? And then we could try to reproduce your issue.

    Best Regards,

    Edward

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 09, 2017 6:45 AM
    Moderator
  • Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Cells.Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table!R1C1:R1048576C9", Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
            :=xlPivotTableVersion14
        Sheets("Sheet4").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("ROLE")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("CLASS")
            .Orientation = xlRowField
            .Position = 2
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("ES")
            .Orientation = xlRowField
            .Position = 3
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("CLASSACCESS")
            .Orientation = xlRowField
            .Position = 4
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("PROPERTY/RELATION")
            .Orientation = xlRowField
            .Position = 5
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("RELES")
            .Orientation = xlRowField
            .Position = 6
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("RELCLASS")
            .Orientation = xlRowField
            .Position = 7
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("ACCESS")
            .Orientation = xlRowField
            .Position = 8
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("SORT")
            .Orientation = xlRowField
            .Position = 9
        End With
        ActiveSheet.PivotTables("PivotTable1").ShowDrillIndicators = False
        With ActiveSheet.PivotTables("PivotTable1")
            .ColumnGrand = False
            .RowGrand = False
        End With
        ActiveSheet.PivotTables("PivotTable1").PivotFields("ROLE").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("CLASS").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("ES").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("CLASSACCESS").Subtotals = _
            Array(False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("PROPERTY/RELATION"). _
            Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
            False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("RELES").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("RELCLASS").Subtotals = _
            Array(False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("ACCESS").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SORT").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
        ActiveWindow.SmallScroll Down:=384
        Sheets("Sheet4").Select
        Sheets("Sheet4").Name = "Engineering Scenario"
        Columns("I:I").Select
        Range("I385").Activate
        ActiveWindow.SmallScroll Down:=-309
        Selection.EntireColumn.Hidden = True
        ActiveWindow.ScrollRow = 1
    '   ChDir "C:\Users\z003jvum\Desktop"
    End Sub
    

    Friday, March 10, 2017 10:15 AM
  • Hello Edward,

    Thank you for your response.

    Yes, PivotCaches .Create work with VBA  but not with C#. 

    VBA

     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table!R1C1:R1048576C9", Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
            :=xlPivotTableVersion14

    C#

    Excel.PivotCache oPivotCache = (Excel.PivotCache)excelWorkBook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, oRange);

    Please also let me know how can i share the file with you.

    Thank you.

    Friday, March 10, 2017 10:35 AM
  • Sorry for late response, it wastes time to make a test.

    As a result, I could reproduce your issue under Excel 2010, and this issue does not exist under Excel 2016.

    I suggest you try to test this function under Excel 2016.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 14, 2017 8:27 AM
    Moderator
  • The application is used by so many users and They all are using Excel 2010, It will not comfortable for them to upgrade there Excel just for this issue.

    We have to find a workaround for the same so After doing more research I found that various Excel Version libraries have different data limit  as mentioned below:

    Version

    Max. Rows

    Max. Columns

    Max. Cols by letter

    Excel 365*

    1,048,576

    16,384

    XFD

    Excel 2013

    1,048,576

    16,384

    XFD

    Excel 2010

    1,048,576

    16,384

    XFD

    Excel 2007

    1,048,576

    16,384

    XFD

    Excel 2003

    65,536

    256

    IV

    Excel 2002 (XP)

    65,536

    256

    IV

    Excel 2000

    65,536

    256

    IV

    Excel 97

    65,536

    256

    IV

    Excel 95

    16,384

    256

    IV

    Excel 5

    16,384

    256

    IV


    Excel 97-2003 has data range limit of 256 columns and 65,536 rows which caused problem when we assign more than this data range to a pivot cache create method. Whereas Excel 2010 has data range limit of  16,384 columns and 10,485,756 rows.

    After adding Excel 14.0 library as reference why it is still limiting the data range of excel 97-2003 version? How to force the Source code to use the Excel 2010 data range limit?

    Tuesday, March 21, 2017 3:42 AM
  • Hello,

    The issue does exist. And I try to add the same dll and also try to create a project in VS2015/Office2016 and then test it with Office 2010. Unfortunately, the problem still exists. I think it is an issue in Excel automation. Sorry that we failed to find any workaround for Office2010. Due to the limitation of community support channel, I suggest you submit your feedback on Office Developer Platform UserVoice site: https://officespdev.uservoice.com/

    Sorry for any inconvenience and thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 8:30 AM
    Moderator