472,805 Members | 854 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

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"
'Worksheet.Sheets("SMS").Width = 10
stDB = ThisWorkbook.path & "\" & "GSM_All.mdb"


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
'Worksheets("SMS").Visible = True

Set rst = Nothing
Set cnt = Nothing

Application.ScreenUpdating = True
Oct 28 '08 #1
6 8813

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.



Oct 28 '08 #2
Stewart Ross
2,545 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.

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

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
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.

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

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

Similar topics

by: Ralph | last post by:
I'm trying to import a range of cells from an Excel spreadsheet into a table in access. The first row contains column labels, but I cannot use those as my field names, both because of their format...
by: Macca | last post by:
Hi, I have a table which has a date/time field. I am storing them as follows :- 01/01/2005 11:25 01/01/2005 19:44 02/01/2005 05:04
by: kim | last post by:
Hello! Here, in brief, is my problem. Access 2002. I need to export to a csv text file with text delimiters on every field. I find that if I save the original data in Excel and make sure the...
by: John Brock | last post by:
I am creating an Excel workbook using VB.NET, and have run into a problem. Excel at times insists on reformatting data that I enter into cells, e.g., converting "01234" to "1234", and this screws...
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
by: michaelw118 | last post by:
Hi, I hope someone can give me some solutions to these problems I faced. I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in...
by: chrspta | last post by:
I am new to Visual basic. I need a program using VB6 that converts txt files to excel file.Description is in the below: The form should have the Drive list, Dir list, file list and cmdConvert...
by: Brian | last post by:
Hello, I am new to Microsoft access 2007 and I have created a new database. There is a dollar about that I have to change from text to currency format. Also, there is a zip code that I have to...
by: franc sutherland | last post by:
Hi everyone, I am using Access 2003. I have a database with a table in it which is linked to an excel spreadsheet. When I install the database on someone else's system, the pathname to the...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.