locked
Turn off automatic capitalizing programmatically in Excel RRS feed

  • Question

  • Hi All,

    I created a PivotTable with two columns using C#. 

    this is the source data:

    and this is the created PivotTable:

    the last cell must be a lowercase "a" and not the uppercase "A".

    is there any custom config I can perform at the when creating the pivotTable to avoid this automatic capitalizing?

    I appreciate advance your helpful ideas


    said

    Monday, December 28, 2015 5:11 PM

Answers

  • >>>Also I have tried to refresh the PivotTable from the code after the creation but it doesn't help.

    Sorry for my mistake, as far as I know that PivotTables are not case sensitive, so I suggest that this workaround is possible to make the two entries different by adding on or more spaces.

    In addition, if you have any feedback about Excel, you can submit them from link below:

    Excel UserVoice

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:50 AM
    Wednesday, December 30, 2015 9:46 AM

All replies

  • >>>the last cell must be a lowercase "a" and not the uppercase "A".

    According to your description, I have made a sample with VBA to try to reproduce your issue, unfortunately, I can't. So I suggest that you could create a PivotTable manually in Excel,
    Could you reproduce this issue? Otherwise you could change letter "A" to "a"  in Range  then refresh PivotTable, you could refer to below code to refresh PivotTable:

    Sub PivotMacro()
    Dim pt As PivotTable
        Set pt = ActiveSheet.PivotTables("MyPivot")
        pt.RefreshTable
    End Sub

    In addition could you provide your sample code how to create PivotTable, that will help us reproduce and resolve your issue.
    Thanks for your understanding.

    Tuesday, December 29, 2015 6:23 AM
  • Hi, 

    Thanks for your response.

    When I created a pivotTable manually I get the same result. But when I change any value in my datasource (not necessary the "a") and then I refresh, I get also the "a" refreshed to be "A". Strange!!

    Also I have tried to refresh the PivotTable from the code after the creation but it doesn't help.

    Here is my code:

    try{
         String rangInR1C2 = this.dataWorkSheet.Name + "!" + myApp.ConvertFormula(pivotData.Address, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing) as String;
         pivotTable = this.myApp.ActiveWorkbook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, rangInR1C2, 4).CreatePivotTable(pivotDestination, pivotTableName);
       }
       catch (Exception e)
       {
           pivotTableSheet.PivotTableWizard(
                  XlPivotTableSourceType.xlDatabase,
                  pivotData,
                  pivotDestination,
                  pivotTableName,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing,
                  Type.Missing
                  );
                    }
    
                  pivotTable = (PivotTable)pivotTableSheet.PivotTables(pivotTableName);
                    pivotTable.Format(XlPivotFormatType.xlPTNone);
                    pivotTable.RefreshTable();
    
    Thanks for your help.





    said

    Tuesday, December 29, 2015 10:46 AM
  • >>>Also I have tried to refresh the PivotTable from the code after the creation but it doesn't help.

    Sorry for my mistake, as far as I know that PivotTables are not case sensitive, so I suggest that this workaround is possible to make the two entries different by adding on or more spaces.

    In addition, if you have any feedback about Excel, you can submit them from link below:

    Excel UserVoice

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:50 AM
    Wednesday, December 30, 2015 9:46 AM