Answered by:
Data convert to number using vb.net
Question

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
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

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 
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")
 Edited by Bernie Deitrick, Excel MVP 20002010 Thursday, May 14, 2015 3:33 PM
 Proposed as answer by ryguy72 Thursday, May 14, 2015 4:39 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

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
 Edited by Bernie Deitrick, Excel MVP 20002010 Thursday, May 14, 2015 5:26 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

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

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


