locked
Excel Formatting through VSTO Project RRS feed

  • Question

  • Hello All,

    Trying to format excel using the below code but it doesn't work in a VSTO c# project. Any help greatly appreciated.

                Excel.Range rg8 = (Excel.Range)this.Cells[1, 8];
                rg8.EntireColumn.NumberFormat = "mm/dd/yy;@";

                Excel.Range rg12 = (Excel.Range)this.Cells[1, 12];
                rg12.EntireColumn.NumberFormat = "0%;[Red]-0%";

    Regards,
    Will

    Monday, July 27, 2015 8:19 PM

Answers

  • Hi William,

    Thanks for the detail information about this issue.

    >>The reason for using VSTO and Excel is so that I can have x2 c# apps as buttons on the Excel Summary page.<<

    It seems you were developing an document-level customization. I am trying to rerpoduce this issue by creating document-level VSTO customization.

    Here are the detail steps:
    1. Create a Excel 2013 Workbook VSTO project

    2. Set the A1, A2 with formula '=ToDay()' on the Sheet1

    3. Set the B1, B2 with 30%, -30% on the Sheet1

    4. Drag a WinForm button on the toolbox into Sheet1

    5. Add the click event for the button like code below:

     Globals.Sheet1.Range["A1"].EntireColumn.NumberFormat = "mm/dd/yy;@";
     Globals.Sheet1.Range["B1"].EntireColumn.NumberFormat = "0%;[Red]-0%";

    The detail information you mentioned above seems like the output when you building the project. Did you try to debug the project and get any exception?

    Would you mind sharing more detail about how we can reproduce this issue.

    Regards & Fei


    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.

    • Marked as answer by kencokid Wednesday, July 29, 2015 10:41 AM
    Wednesday, July 29, 2015 5:59 AM

All replies

  • Hi Will,

    Did you get any error message? I am trying to reproduce this issue using VBA in Excel 2013(English language), however it is failed. Here is the steps for the testing:
    1. Set the A1, A2 with formula '=ToDay()'

    2. Set the B1, B2 with 30%, -30%

    3. Run the code below:

    Range("A1").EntireColumn.NumberFormat = "mm/dd/yy;@"
    Range("B1").EntireColumn.NumberFormat = "0%;[Red]-0%"

    Which version of Excel are you using? Can you reproduce this issue like steps above?

    Regards & Fei


    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.


    Tuesday, July 28, 2015 10:03 AM
  • Hello Fei,<o:p></o:p>

    Really appreciate you responding, as for VBA the formatting works fine. The reason for using VSTO and Excel is so that I can have x2 c# apps as buttons on the Excel Summary page.<o:p></o:p>

    The only errors / warnings I'm seeing is as follows.<o:p></o:p>

    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\Microsoft.Mashup.Client.Excel.dll'. Cannot find or open the PDB file.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\assembly\GAC\Extensibility\7.0.3300.0__b03f5f7f11d50a3a\Extensibility.dll'. Module was built without symbols.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\assembly\GAC_MSIL\office\15.0.0.0__71e9bce111e9429c\office.dll'. Module was built without symbols.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\Microsoft.Mashup.Client.Windows.dll'. Cannot find or open the PDB file.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\Microsoft.Mashup.Document.dll'. Cannot find or open the PDB file.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\Microsoft.Mashup.OleDbProvider.dll'. Cannot find or open the PDB file.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\Microsoft.Mashup.Engine.Interface.dll'. Cannot find or open the PDB file.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\EventSource.dll'. Cannot find or open the PDB file.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll'. Module was built without symbols.
    'Excel.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Program Files\Microsoft Power Query for Excel\bin\Microsoft.Mashup.Engine.1.dll'. Cannot find or open the PDB file.<o:p></o:p>

    Kind Regards,<o:p></o:p>

    William Brown<o:p></o:p>


    Tuesday, July 28, 2015 6:34 PM
  • Hello Fei,

    I'm using Visual Studio 2013 and Excel 2013.

    Regards,

    William Brown

    Tuesday, July 28, 2015 7:16 PM
  • Hi William,

    Thanks for the detail information about this issue.

    >>The reason for using VSTO and Excel is so that I can have x2 c# apps as buttons on the Excel Summary page.<<

    It seems you were developing an document-level customization. I am trying to rerpoduce this issue by creating document-level VSTO customization.

    Here are the detail steps:
    1. Create a Excel 2013 Workbook VSTO project

    2. Set the A1, A2 with formula '=ToDay()' on the Sheet1

    3. Set the B1, B2 with 30%, -30% on the Sheet1

    4. Drag a WinForm button on the toolbox into Sheet1

    5. Add the click event for the button like code below:

     Globals.Sheet1.Range["A1"].EntireColumn.NumberFormat = "mm/dd/yy;@";
     Globals.Sheet1.Range["B1"].EntireColumn.NumberFormat = "0%;[Red]-0%";

    The detail information you mentioned above seems like the output when you building the project. Did you try to debug the project and get any exception?

    Would you mind sharing more detail about how we can reproduce this issue.

    Regards & Fei


    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.

    • Marked as answer by kencokid Wednesday, July 29, 2015 10:41 AM
    Wednesday, July 29, 2015 5:59 AM
  • Hello Fei,

    Problem solved it looks like using Globals is the way to go, I've been using that for a number of other references but obviously should have been consistent in my coding. I'll also research Globals further for a better understanding.

    Thank you very much you have saved my day honestly. 

    Kind Regards,

    Will

    Wednesday, July 29, 2015 10:17 AM