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

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

--
Regards,
Tom Ogilvy


"Casper Hornstrup" wrote in 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

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


.

Hi Casper,

Use numberformat to change cell format.

mycell.Value = CStr(Replace(mycell.Value, ",", ""))
mycell.NumberFormat = "@" 'Change to Text Format


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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


.

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

just for interest.

--
Regards,
Tom Ogilvy



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


.