467,145 Members | 1,053 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,145 developers. It's quick & easy.

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

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
Oct 28 '08 #1
  • viewed: 7869
Share:
6 Replies
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
Oct 28 '08 #2
Expert Mod 2GB
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
Oct 28 '08 #3
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
Oct 29 '08 #4
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
Oct 29 '08 #5
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
Oct 29 '08 #6
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
Oct 30 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Macca | last post: by
1 post views Thread by kim@corrupt.net | last post: by
7 posts views Thread by franc sutherland | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.