How do I change the datatype of a cell?

C

Casper Hornstrup

I need to change the datatype of all cells in a column to text. I have the
following VBscript:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call UpdateDatatypes
End Sub

Private Sub Workbook_Open()
Call UpdateDatatypes
End Sub

Private Sub UpdateDatatypes()
Dim myCell As Excel.Range
Dim myRng As Excel.Range
Dim wks As Excel.Worksheet

For Each wks In Me.Worksheets
Set myRng = wks.UsedRange.Cells

For Each myCell In myRng.Cells
If (Not Intersect(myCell, wks.Range("h:n")) Is Nothing) Then
myCell.Value = CStr(Replace(myCell.Value, ",", "."))
End If
If (Not Intersect(myCell, wks.Range("a:b")) Is Nothing) Then
myCell.Value = CStr(Replace(myCell.Value, ",", "."))
End If
Next
Next
End Sub

Now, any cells that have only digits in them will be converted to a number
type by Excel.
If I use:

myCell.Value = "A" & CStr(Replace(myCell.Value, ",", "."))

then cells will have a text type after the script is run. How do I keep
excel from changing the
type of the cells with only digits in them to number?

I need this because the MS JET OleDb provider for excel is stupid and will
only allow
reading of a cell if it has the same type as the the cell in same column in
the previous row
(if not it returns an empty string).

Casper
 
T

Tom Ogilvy

Private Sub UpdateDatatypes()
Dim myCell As Excel.Range
Dim myRng As Excel.Range
Dim wks As Excel.Worksheet

For Each wks In ThisWorkbook.Worksheets
Set myRng = Intersect(wks.UsedRange, wks.Range("A:B,H:N"))

For Each myCell In myRng
myCell.NumberFormat = "@"
myCell.Value = "'" & CStr(Replace(myCell.Text, ",", "."))
Next
Next
End Sub

Assuming you still want to replace any comma with a period/fullstop
 
K

Klaus

In the following macro to each value of the defined cells
is "'" added. Due to this it gets a string.

Sub MakeString()
x = 4
For i = 1 To x
Cells(i, 1).Value = "'" & Cells(i, 1).Value
Next
End Sub

Regards
Klaus
 
C

Casper Hornstrup

Thanks Klaus. This worked beatifully.

Also thanks to you all for the NumberFormat solution.
I'm sure I'm going to need that sometime.
 
T

Tom Ogilvy

Note that my code
myCell.Value = "'" & CStr(Replace(myCell.Text, ",", "."))
also included the single quote

just for interest.
 
Top