none
Data convert to number using vb.net RRS feed

  • Question

  • Hi,

    How to convert the green triangle data into number by using vb.net?

           


    Thanks

    Thursday, May 14, 2015 8:16 AM

Answers

  • Between these two line, you need to actually set the value of "constant" - as is, the variable is null.


            Dim constant As Excel.xlCellType       
    
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)

    Something like

    constant = Excel.xlCellType.xlCellTypeConstants

            Dim constant As Excel.xlCellType       
            constant = Excel.xlCellType.xlCellTypeConstants
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)

    I am not sure of the exact syntax of that line

    • Marked as answer by Hwai17 Friday, May 15, 2015 4:11 PM
    Friday, May 15, 2015 1:46 PM

All replies

  • Don't know about VB.net, but this is the VBA, which you can use to guide you to the correct objects and methods:

    With Range("A:A").SpecialCells(xlCellTypeConstants, 2)
        .NumberFormat = "General"
        .Value = .Value
    End With

    • Proposed as answer by ryguy72 Thursday, May 14, 2015 4:38 PM
    Thursday, May 14, 2015 1:17 PM
  • Don't know about VB.net, but this is the VBA, which you can use to guide you to the correct objects and methods:

    With Range("A:A").SpecialCells(xlCellTypeConstants, 2)
        .NumberFormat = "General"
        .Value = .Value
    End With

    Hi Bernie,

    It's can't work for vb.net.

    Dim xlsApp As New Excel.Application
            xlsApp.Visible = True
            Dim xlsWorkbook As Excel.Workbook = xlsApp.Workbooks.Open("C:\Users\hwai\Desktop\Complete Chart\Table1.xlsx")
            Dim constant As Excel.Constants
            With range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With

    Thanks

    Thursday, May 14, 2015 2:10 PM
  • It can work, but in my experience (years ago now) you need to be much more explicit with your declarations and usages - you need set the constant to a value, like

    constant = Excel.xlCellTypeConstants

    and fully qualify the range

    xlsWorkbook.Worksheets(1).Range("A:A")



    Thursday, May 14, 2015 3:32 PM
  • It can work, but in my experience (years ago now) you need to be much more explicit with your declarations and usages - you need set the constant to a value, like

    constant = Excel.xlCellTypeConstants

    and fully qualify the range

    xlsWorkbook.Worksheets(1).Range("A:A")



    Hi Bernie,

    There is an error on  'constant = Excel.xlCellTypeConstants'. Please advice.

    Dim xlsApp As New Excel.Application
            xlsApp.Visible = True
            Dim xlsWorkbook As Excel.Workbook = xlsApp.Workbooks.Open("C:\Users\hwai\Desktop\Complete Chart\Table1.xlsx")
            Dim constant As Excel.Constants
            Dim range As Excel.Range
    
            constant = Excel.xlCellTypeConstants
            xlsWorkbook.Worksheets(1).Range("A:A")
    
            With range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With
           
        End Sub

    Thanks

    Thursday, May 14, 2015 5:13 PM
  • If you have autocomplete on, try typing .xlc and see what comes up - it may be that you get a class of constants , of which your desired constant is a member, like

     Excel.xlCellType.xlCellTypeConstants

    but you should try to match your declaration to the actual type

            Dim constant As Excel.xlCellType

    You probably also have some object explorer capability, so you should be able to see the classes and values available.

    Also, you will need to change

            xlsWorkbook.Worksheets(1).Range("A:A")
    
            With range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With

    to something like

            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With



    Thursday, May 14, 2015 5:20 PM
  • If you have autocomplete on, try typing .xlc and see what comes up - it may be that you get a class of constants , of which your desired constant is a member, like

     Excel.xlCellType.xlCellTypeConstants

    but you should try to match your declaration to the actual type

            Dim constant As Excel.xlCellType

    You probably also have some object explorer capability, so you should be able to see the classes and values available.

    Also, you will need to change

            xlsWorkbook.Worksheets(1).Range("A:A")
    
            With range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With

    to something like

            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With



    Hi Bernie,

    Still get an error.

       Dim xlsApp As New Excel.Application
            xlsApp.Visible = True
            Dim xlsWorkbook As Excel.Workbook = xlsApp.Workbooks.Open("C:\Users\hwai\Desktop\Complete Chart\Table1.xlsx")
            Dim constant As Excel.xlCellType       
    
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)
                .NumberFormat = "General"
                .Value = .Value
            End With

    Thanks

    Friday, May 15, 2015 1:37 AM
  • Between these two line, you need to actually set the value of "constant" - as is, the variable is null.


            Dim constant As Excel.xlCellType       
    
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)

    Something like

    constant = Excel.xlCellType.xlCellTypeConstants

            Dim constant As Excel.xlCellType       
            constant = Excel.xlCellType.xlCellTypeConstants
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)

    I am not sure of the exact syntax of that line

    • Marked as answer by Hwai17 Friday, May 15, 2015 4:11 PM
    Friday, May 15, 2015 1:46 PM
  • Between these two line, you need to actually set the value of "constant" - as is, the variable is null.


            Dim constant As Excel.xlCellType       
    
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)

    Something like

    constant = Excel.xlCellType.xlCellTypeConstants

            Dim constant As Excel.xlCellType       
            constant = Excel.xlCellType.xlCellTypeConstants
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)

    I am not sure of the exact syntax of that line

    Hi,

    The green triangle still there nothing change.

     Dim xlsApp As New Excel.Application
            xlsApp.Visible = True
            Dim xlsWorkbook As Excel.Workbook = xlsApp.Workbooks.Open("C:\Users\hwai\Desktop\Complete Chart\Book1.xlsx")
            Dim xlsWorkSheet As Excel.Worksheet = xlsWorkbook.Worksheets(1)
    
            Dim constant As Excel.XlCellType
            constant = Excel.XlCellType.xlCellTypeConstants
            With xlsWorkbook.Worksheets(1).Range("A:A").SpecialCells(constant, 2)
    
            End With
    
           
            xlsWorkbook.Save()
            xlsApp.Quit()


    Thanks

    Friday, May 15, 2015 3:47 PM
  • What happened to this part of your code?

               .NumberFormat = "General"
                .Value = .Value

    Friday, May 15, 2015 4:02 PM
  • What happened to this part of your code?

               .NumberFormat = "General"
                .Value = .Value

    Hi Bernie,

    It's working. I'm missing that part,sorry about that. Thank you so much.

    Friday, May 15, 2015 4:11 PM
  • Great!   Thanks for letting us know that you got it working.
    Friday, May 15, 2015 4:24 PM