Dear all,

I am not sure if I am posting my question in the right page, I hope someone
can help me out.
I used the DTS package in SQL server to put a table's data on an Excel
worksheet. However, I discover that a single quote " ' " is placed in front
of the values in every cell. This makes the calculation or format change
cannot be carried out. How can I get rid of the single quote? I am so
frustrated.
Thanks.

Ivan

Hi Ivan,

Try:

'===============
Public Sub Tester001()
Dim rng As Range
Dim rCell As Range
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<======= CHANGE
Set SH = WB.Sheets("Sheet1") '<<======= CHANGE
Set rng = SH.Range("A1:D20") '<<======= CHANGE

For Each rCell In rng.Cells
With rCell
If IsNumeric(.Value) Then
.Value = .Value
End If
End With
Next rCell

End Sub
'<<===============


---
Regards,
Norman



"Ivan" wrote in message
...
Dear all,

I am not sure if I am posting my question in the right page, I hope
someone
can help me out.
I used the DTS package in SQL server to put a table's data on an Excel
worksheet. However, I discover that a single quote " ' " is placed in
front
of the values in every cell. This makes the calculation or format change
cannot be carried out. How can I get rid of the single quote? I am so
frustrated.
Thanks.

Ivan

Hi Ivan,

Or, perhaps preferable would be:
'===============
Public Sub Tester002()
Dim rng As Range
Dim rCell As Range
Dim WB As Workbook
Dim sh As Worksheet

Set WB = ActiveWorkbook '<<======= CHANGE
Set sh = WB.Sheets("Sheet1") '<<======= CHANGE
Set rng = sh.Range("A1:D20") '<<======= CHANGE

For Each rCell In rng.Cells
With rCell
If .PrefixCharacter = "'" Then
.Formula = .Value
End If
End With
Next rCell

End Sub
'<<===============


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Ivan,

Try:

'===============
Public Sub Tester001()
Dim rng As Range
Dim rCell As Range
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<======= CHANGE
Set SH = WB.Sheets("Sheet1") '<<======= CHANGE
Set rng = SH.Range("A1:D20") '<<======= CHANGE

For Each rCell In rng.Cells
With rCell
If IsNumeric(.Value) Then
.Value = .Value
End If
End With
Next rCell

End Sub
'<<===============


---
Regards,
Norman



"Ivan" wrote in message
...
Dear all,

I am not sure if I am posting my question in the right page, I hope
someone
can help me out.
I used the DTS package in SQL server to put a table's data on an Excel
worksheet. However, I discover that a single quote " ' " is placed in
front
of the values in every cell. This makes the calculation or format change
cannot be carried out. How can I get rid of the single quote? I am so
frustrated.
Thanks.

Ivan

select all of the cells and do a find and replace for ' and replace with ""
(that's 2 double quotes).

--


Gary


"Ivan" wrote in message
...
Dear all,

I am not sure if I am posting my question in the right page, I hope
someone
can help me out.
I used the DTS package in SQL server to put a table's data on an Excel
worksheet. However, I discover that a single quote " ' " is placed in
front
of the values in every cell. This makes the calculation or format change
cannot be carried out. How can I get rid of the single quote? I am so
frustrated.
Thanks.

Ivan