none
COM automation - Convert to number RRS feed

  • Question

  • Hi All

    I have seen many approximate examples of this on the web but none I've found do the EXACT operation ?

    If I have the following left justified text in a cell £4,500.23 that has the little green triangle in the corner   .....

    If as a normal Excel user you call up the drop down and choose "Convert to number" ....

    The underlying data for the cell is changed to 4500.23 BUT it retains the formatting so you now have £4,500.23 right justified

    This is what I want

    How do I perform this EXACT operation via COM automation 

    This example is on a single cell but I would want to do all cells with green triangle, SpecialCells seems to allow me to selct them BUT I've not found the EXACT "Convert to number" operation to perform on them

    Thanks in advance

    Tuesday, June 28, 2016 3:06 PM

Answers

  • Hi Minieggs1999,

    please use the code below will work as per your requirement.

     static void Main(string[] args)
            {
                Excel.Application excelApp = new Excel.Application();          
                excelApp.Visible = true;         
                string workbookPath = @"C:\Users\v-padee\Desktop\test11.xlsx";
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                    0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);         
                Excel.Sheets excelSheets = excelWorkbook.Worksheets;         
                string currentSheet = "Sheet1";
                Excel.Worksheet excelWorksheet =
                    (Excel.Worksheet)excelSheets.get_Item(currentSheet);        
                Excel.Range excelCell =
                      (Excel.Range)excelWorksheet.get_Range("A1", "A10");
                excelCell.NumberFormat= "£ #,###.00 ";
            }

    it will give you output like below.

    Regards

    Deepak


    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, July 7, 2016 9:18 AM
    Moderator

All replies

  • Hi Minieggs1999,

    From your description of the thread it looks like you want to change the Number Format of the range.

    so for examples and some information regarding Number Format property please visit the link below.

    Range.NumberFormat Property (Excel)

    in the following link you can find the example of it.

    How to automate Microsoft Excel from Microsoft Visual C#.NET

    Hope this helps you to solve your issue.

    Regards

    Deepak


    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.

    Wednesday, June 29, 2016 3:41 AM
    Moderator
  • I have two alternative (maybe the second is faster than the first):

    Private Sub ConvertToNumber(pRange As Range) Dim iCell As Range For Each iCell In pRange.Cells iCell.Value2 = iCell.Value2 Next iCell End Sub

    'Or

    Private Sub ConvertToNumber(pRange As Range) Dim rBlank As Range With pRange.Worksheet .UsedRange(.UsedRange.CountLarge).Offset(1, 1).Copy End With pRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd End Sub



    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, June 29, 2016 11:31 AM
  • The 'best' answer seems to be use TextToColumns on a Selected Range that is one WHOLE column otherwise any blank cells in the Range give you an error (even though it converts the rest)
    Wednesday, June 29, 2016 1:07 PM
  • You can first start a macro recording, then do the desired operation. Now stop the macro recording and look at the resulting VBA code. You'll find the Macro Recorder on the Developer tab.
    If you do not have the developer tab visible:

    https://msdn.microsoft.com/en-us/library/bb608625.aspx


    Best regards, George

    Wednesday, June 29, 2016 2:19 PM
  • Sorry I did not see these replies when I posted mine

    Yes I had tried the above technique but it does not retain the currency formatting

    Wednesday, June 29, 2016 3:49 PM
  • This works unless the first cell in the selected column is empty in which case it raises an error

    I can't think of a workaround other than putting a 1 in it and removing it again ?

    Wednesday, June 29, 2016 3:51 PM
  • @Minieggs1999,

    Which code/solution are you talking about?


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, June 29, 2016 5:18 PM
  • Hi Minieggs1999,

    please use the code below will work as per your requirement.

     static void Main(string[] args)
            {
                Excel.Application excelApp = new Excel.Application();          
                excelApp.Visible = true;         
                string workbookPath = @"C:\Users\v-padee\Desktop\test11.xlsx";
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                    0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);         
                Excel.Sheets excelSheets = excelWorkbook.Worksheets;         
                string currentSheet = "Sheet1";
                Excel.Worksheet excelWorksheet =
                    (Excel.Worksheet)excelSheets.get_Item(currentSheet);        
                Excel.Range excelCell =
                      (Excel.Range)excelWorksheet.get_Range("A1", "A10");
                excelCell.NumberFormat= "£ #,###.00 ";
            }

    it will give you output like below.

    Regards

    Deepak


    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, July 7, 2016 9:18 AM
    Moderator