none
VB.NET Excel Interop "Convert to Number" Column RRS feed

  • Question

  • I have a formula that I am passing {=MAX(IF(F:F=""3P01"",H:H))} but the H column is text. how do I convert to number  programmably? for example below.  any help is greatly appreciated!

     MyExcel.Range("W1").Activate()
     MyExcel.Range("W1").NumberFormat = "0"
     MyExcel.ActiveCell.FormulaArray = "=MAX(IF(F:F=""3P01"",H:H))"


    Work Smarter Not Harder

    Monday, August 15, 2016 10:12 PM

Answers

  • Hi jimbrown.ws,

    you can use the code below. it will convert the column to number.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim xlsApp As New Excel.Application
            xlsApp.Visible = True
            Dim xlsWorkbook As Excel.Workbook = xlsApp.Workbooks.Open("C:\Users\v-padee\Desktop\demo.xlsx")
            Dim constant As Excel.XlCellType
            constant = Excel.XlCellType.xlCellTypeConstants
    
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With
        End Sub

    The code is taken from the link below in which another user had the issue like you.

    Data convert to number using vb.net

    I had tested the code and it worked for me.

    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.

    • Marked as answer by jimbrown.net Tuesday, August 16, 2016 12:41 PM
    Tuesday, August 16, 2016 2:17 AM
    Moderator

All replies

  • Hi jimbrown.ws,

    you can use the code below. it will convert the column to number.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim xlsApp As New Excel.Application
            xlsApp.Visible = True
            Dim xlsWorkbook As Excel.Workbook = xlsApp.Workbooks.Open("C:\Users\v-padee\Desktop\demo.xlsx")
            Dim constant As Excel.XlCellType
            constant = Excel.XlCellType.xlCellTypeConstants
    
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With
        End Sub

    The code is taken from the link below in which another user had the issue like you.

    Data convert to number using vb.net

    I had tested the code and it worked for me.

    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.

    • Marked as answer by jimbrown.net Tuesday, August 16, 2016 12:41 PM
    Tuesday, August 16, 2016 2:17 AM
    Moderator
  • Thank you,   Deepak!  that worked very well.   

    Work Smarter Not Harder

    Tuesday, August 16, 2016 12:41 PM