changing of text format to number format when data is extracted from access to excel

[and1]

hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem is when i use the extracted data to create a chart using vba and it doesn't seem to display the data properly.. it is due to the text format when the data is extracted to excel..
how can i change it to number format when the table of data is extracted ..

Here's the code:

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wssheet As Worksheet
Dim lnNumberOfField As Long, lnCount As Integer
Dim Target As Range
Dim Worksheet As Worksheet

Set wssheet = ThisWorkbook.Worksheets("SMS")
'wsSheet.Name = "Download"
'Application.DoubleClick
'Worksheet.Sheets("SMS").Width = 10
stDB = ThisWorkbook.path & "\" & "GSM_All.mdb"

wssheet.Range("A1").CurrentRegion.clear

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

rst.Open "SELECT * FROM Sms", cnt

lnNumberOfField = rst.Fields.Count

For lnCount = 0 To lnNumberOfField - 1
wssheet.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
Next lnCount

wssheet.Cells(2, 1).CopyFromRecordset rst
Sheets("SMS").Activate
'Worksheets("SMS").Visible = True


Set rst = Nothing
Set cnt = Nothing

Application.ScreenUpdating = True

[emsik1001]

Hi

What I did (it might not suit you 100%) I used ADO to transfer the data to the spreadsheet and in the spreadsheet I have cells which refer to the ADO results but conver them to number.

like

=VALUE(B9)


Regards
Emil

[Stewart Ross]

Hi. The Excel recordset transfer function CopyFromRecordSet does not of itself convert numbers to text - it has much the same effect as simply copying the underlying query manually and pasting the data into Excel. The default format of the cells in an Excel sheet is the General format and numbers are interpreted as numbers wherever possible. CopyFromRecordset simply pours the data into the cells using whatever format is already set.

Check the underlying query or table itself and to see if any processing has been applied that has converted the values, such as the use of Format (a function that actually returns a string value, not a number). You can tell immediately from the alignment of the values if you open the query (or table) concerned; if the numbers are left-aligned instead of right-aligned the values are text strings, not numbers at all.

-Stewart

[and1]

Hi emsik1001.. thanks for responding... hmm do i have to apply =value(B9) in the spreadsheet itself or ? I'm pretty new to excel vba .. hmm is there any alternative way for me to change the format ?
Thanks Stewart Ross Inverness for your advice, i'll look into it ..
hmm thanks for your help .. :D

[emsik1001]

Hi

The quickest way (for me) would be to put extra column against your row results and put =value(A:A) where A:A is the column of your original numbers.

and then use the extra column.

Hope it helps
Emil

[emsik1001]

if you want...you can then copy // paste special (values) this should become number. (this way you can overwrite your original data with number format.

Regards
Emil

[and1]

hey Emil .. i took your advice and tried .. well i copied and paste it as special and it works .. i didnt know it was that easy .. haha .. well you've been a great help .. i really appreciate it .. thanks a lot for your advice .. :D