none
Weird problem with PivotCache, the 65536 rows limit in Source Data and LanguageID RRS feed

  • Question

  • Hi all

    After making deep tests, I arrive to the conclusion that if I change the Application.LanguageSettings.LanguageID(msoLanguageIDUI) I can avoid the '1004' Application-defined or object-defined error when my SourceData has more than 65536 rows.

    If in my Excel 2007 I have the Application.LanguageSettings.LanguageID(msoLanguageIDInstall) value = 2070 (The Portuguese language.)

    And in VBA I do:

             ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
             "Serve!R1C1:R65537C21", Version:=xlPivotTableVersion12).CreatePivotTable _
             TableDestination:="Servico!R3C2", TableName:="SLA", DefaultVersion _
             :=xlPivotTableVersion12

    I get the infamous 1004 error !!  If I change R65537 to 65536 works fine...

    If I change the value of Application.LanguageSettings.LanguageID(msoLanguageIDInstall) value to 1033 (The English US language) I can use R1000000 works also fine!!

    I try to create the PivotTable other way for instance:

        Set PPCC = wkbDCR.PivotCaches.Create(1, wkbDCR.Worksheets(5).Range("$A$1:$U$66000"), 1)
        With PPCC
         Set PPTT = .CreatePivotTable(wkbDCR.Worksheets(4).Range("$B$3"), "SLA", 1)
        End With

    or with Named Ranges doesn't work...

    The solution (bad)  is tell the guys that need the report to change the language set from Excel... :(

    Any sugestion ?

    Thanks

    João

    Wednesday, June 17, 2015 4:20 PM

Answers

  • Hi João,

    I tried the following macro at an en-US Microsoft Office (Excel 12.0.6718.5000 SP3 MSO 12.0.6721.5000).

    Sub test()
     
    Dim worksheet1 As Worksheet
    Dim rawDataworksheet As Worksheet
    Dim pt  As PivotTable
    Set worksheet1 = ActiveWorkbook.Worksheets("Sheet1")
    Set rawDataworksheet = ActiveWorkbook.Worksheets("Sheet2")
    Set pt = worksheet1.PivotTables("Sheet1")
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, rawDataworksheet.UsedRange, xlPivotTableVersion12)

    pt.RefreshTable

    End Sub

    I kept rawDataworksheet.UsedRange more than 65536 , and I see the error "Type Mismatch Error" answered in the forum below

    https://social.msdn.microsoft.com/Forums/en-US/c8a8d8c4-33cd-4cbc-88a8-1e85a54e2ee8/type-mismatch-error-when-creating-a-pivotcache-with-65536-rows-in-range?forum=vsto

    The "Type Mismatch Error" is a known issue. However I could not repro the scenario where the ActiveWorkbook.PivotCaches.Create works beyond 65536.

    Considering the fact we are trying to troubleshoot a specific scenario and Because of its complexity your question this falls into the paid support category which requires a more in-depth level of support.  If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone


    Sangeeth,MSFT

    Monday, July 13, 2015 3:39 PM

All replies

  • Hi João,

    Could you reproduce that issue in other environment?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 18, 2015 7:16 AM
    Moderator
  • Hi Starain,

    Before you wrote to wait... Perhaps I waited too long...

    What you mean for "other environment" ??

    I'm using

    Windows 7 Enterprise - Version 6.1  SP1

    MSO Excel 2007 SP3

    What you want me to change ?

    Regards

    João


    Wednesday, July 8, 2015 4:19 PM
  • Hi,

    Try it in other machine and new file.

    Thursday, July 9, 2015 2:13 AM
  • Hello,

    In dutch we somethimes have a similar problem since column stars with a "C" in English, but "K" in dutch.

    e.g:Serve!R1C1:R65537C21 wouls be English, Serve!R1C1:R65537K21, would be dutch.

    Not sure if this is helpfull,

    wouter

    Thursday, July 9, 2015 3:11 PM
  • In other machines the behavoir is the same....

    The machines I have avaible has the some configuration, I work at large corporation with a few thousands of workstations with the some configurations and all with proper licenses.

    The MSO we use is a portuguese version that some of us prefer to set it up to english language.

    The solution I'vd implemented at "prodution enviroment" is when program arrives at the conclusion that has more than 65536 lines and the workstation has the language settings value=2070, gives a message that can't produce the report and advice to change the language setting before make the report....

    It's the best I can do... with this MSO and enviroment.

    I hope the new versions of MSO has overcame this issue.

    João

    Sunday, July 12, 2015 10:48 PM
  • Hi João,

    I tried the following macro at an en-US Microsoft Office (Excel 12.0.6718.5000 SP3 MSO 12.0.6721.5000).

    Sub test()
     
    Dim worksheet1 As Worksheet
    Dim rawDataworksheet As Worksheet
    Dim pt  As PivotTable
    Set worksheet1 = ActiveWorkbook.Worksheets("Sheet1")
    Set rawDataworksheet = ActiveWorkbook.Worksheets("Sheet2")
    Set pt = worksheet1.PivotTables("Sheet1")
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, rawDataworksheet.UsedRange, xlPivotTableVersion12)

    pt.RefreshTable

    End Sub

    I kept rawDataworksheet.UsedRange more than 65536 , and I see the error "Type Mismatch Error" answered in the forum below

    https://social.msdn.microsoft.com/Forums/en-US/c8a8d8c4-33cd-4cbc-88a8-1e85a54e2ee8/type-mismatch-error-when-creating-a-pivotcache-with-65536-rows-in-range?forum=vsto

    The "Type Mismatch Error" is a known issue. However I could not repro the scenario where the ActiveWorkbook.PivotCaches.Create works beyond 65536.

    Considering the fact we are trying to troubleshoot a specific scenario and Because of its complexity your question this falls into the paid support category which requires a more in-depth level of support.  If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone


    Sangeeth,MSFT

    Monday, July 13, 2015 3:39 PM
  • Sangeeth,

    Unfortunately I can't buy nothing, just looking for free suport.

    Thanks any way.

    João.


    Monday, July 13, 2015 6:14 PM