Thanks Klaus. This worked beatifully.

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

"Klaus" wrote in message
...
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


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


.