Excel Text to Number VBA Code?

[Guest]

Hi,

I got the piece about controlling Excel from Access.

I was recording a macro to see how a text can be converted to a number via
that little exclamation mark in Excel 2003 so something like '1 can be
converted to 1, but when I recorded the macro, it didn't show the code for
it! It just show the select part of the statement.

Does anyone know the code for changing text to number?

Thanks!

 

[Alex Dybenko]

Hi,
but how do you put text into cell?
if I use oSheet.Cells(1,1).Value = 1 - I get 1 as a number in excel
you can also try:
oSheet.Cells(1,1).FormulaRC="=1"

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Hi Alex,

It's actually a download from SAP R3 -> SQL Server -> ODBC to Access

So, the numbers are prefixed with an apostrophe.

Is there a quick way to change the whole column?

 

[John Nurick]

Try something like this (air code)

Sub RemoveApostrophes(R As Excel.Range)
Dim C as Excel.Range

For Each C In R.Cells
C.Formula = C.Formula
Next
End Sub