Solved

Convert Numbers Stored as Text in excell with vb

Posted on 2006-11-02
17
932 Views
Last Modified: 2008-02-01
i copy data from access to excell and the data show on excell alway show Numbers Stored as Text is there any way to change the data to numbers on excell by vb i mean after the data past i always need to convert number stored as text to numbers
this the code in access i used to copy data
For col = 0 To Recordset.Fields.Count - 1
        exSheet.Cells(1, col + 7) = Recordset.Fields(col).Name
    Next col
  exSheet.Range("G2").CopyFromRecordset Recordset
0
Comment
Question by:osama120
  • 6
  • 6
  • 4
  • +1
17 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17865039
to set a particular column to number format, try this

'Convert Column B to number, decimal format.
exSheet.Columns("B").NumberFormat = "0.00"


'change cell A2
exSheet.Range("A2").NumberFormat = "0.00"

etc
0
 

Author Comment

by:osama120
ID: 17865182
this will not work i try it it will show formatt is as i type but  still i have conver number store as text to number
0
 

Author Comment

by:osama120
ID: 17865186
is there any way on the excell level
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17865312
in Excel

click Tools/Error Checking
then click on "Options" when the popup appears

then uncheck "Numbers stored as Text"


does that work?
 
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17865334
Hi osama120,

What you are describing is not normal automation behaviour.
Numbers should go acrosss as numbers.
Is your recordset based on a table or a query?
If it's a table then are you certain the datatypes are number in the table.
If it's a query are you using any Format() functions on your number fields (which would convert them into text).

Pete
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17865340
ok, for the affected columns, u could try this
e.g.

this changes values in C2 to C65365

exSheet.Range("C2:C65365").Value = exSheet.Range("C2:C65365").Value
0
 

Author Comment

by:osama120
ID: 17865595
this the code i use
Dim xlExcel As Object
'Declare a varaible to store worksheet1
Dim exSheet As Excel.Worksheet
Dim Recordset As Object
Dim col As Integer
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("QrySumCountByTypeForm")
qdf.Parameters("[Forms]![DailyQuery]![StartDate]") = [Forms]![DailyQuery]![StartDate]
qdf.Parameters("[Forms]![DailyQuery]![EndDate]") = [Forms]![DailyQuery]![EndDate]
Set Recordset = qdf.OpenRecordset
Set xlExcel = CreateObject("excel.application")
   xlExcel.Workbooks.Open "C:\Book1.xls"
 
    xlExcel.Visible = True
    xlExcel.displayalerts = False
Set exSheet = xlExcel.Worksheets(1)
 For col = 0 To Recordset.Fields.Count - 1
        exSheet.Cells(1, col + 7) = Recordset.Fields(col).Name
    Next col
  exSheet.Range("G2").CopyFromRecordset Recordset
xlExcel.Visible = True

Set xlExcel = Nothing

when i go to the worksheet  and check the data i copy it will show me the the number stored as text aand i have to converted


0
 
LVL 77

Expert Comment

by:peter57r
ID: 17865622
Please post the sql for QrySumCountByTypeForm.

Pete
0
 

Author Comment

by:osama120
ID: 17865631
SELECT TypeNames.TypeName, NZ(QrySumCountByType.CountOfRecordLoan,0) AS CountOfRecordLoan, Nz(QrySumCountByType.SumOfRecordLoan,0) AS SumOfRecordLoan
FROM QrySumCountByType RIGHT JOIN TypeNames ON QrySumCountByType.TypeName=TypeNames.TypeName;
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17865704
try adding this line

  exSheet.Range("B2:C65365").Value = exSheet.Range("B2:C65365").Value

after your line here

 exSheet.Range("G2").CopyFromRecordset Recordset



or, I doubt it makes a difference but give it a go


SELECT TypeNames.TypeName, VAL(NZ(QrySumCountByType.CountOfRecordLoan,0)) AS CountOfRecordLoan, VAL(Nz(QrySumCountByType.SumOfRecordLoan,0)) AS SumOfRecordLoan
FROM QrySumCountByType RIGHT JOIN TypeNames ON QrySumCountByType.TypeName=TypeNames.TypeName;
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 17865737
Well that just moves the issue back another level and the same questions remain.
You clearly have 2 choices - either to stop being concerned about the cause of the problem and just create an excel macro to fix the columns or to try to discover the cause and see if it can be resolved freom that direction.

I confess that I am starting to move towards the first of these in which case you should pick up what rocki was doing.

If you still want to purse the cause, I would test the query QrySumCountByTypeForm by converting it to a make-table query and looking at the datatypes produced by access.  If the datatypes produced are correct then you might want to change your recordset to be based on the table created.

Pete
0
 

Author Comment

by:osama120
ID: 17865742
thank u so match it work
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17865945
Im confused, what did u to get it to work?
Did u perform a make table query?
0
 

Author Comment

by:osama120
ID: 17866226
 use the select statement and the Val Function so when copy to excell the number Remains numbers
SELECT TypeNames.TypeName, VAL(NZ(QrySumCountByType.CountOfRecordLoan,0)) AS CountOfRecordLoan, VAL(Nz(QrySumCountByType.SumOfRecordLoan,0)) AS SumOfRecordLoan
FROM QrySumCountByType RIGHT JOIN TypeNames ON QrySumCountByType.TypeName=TypeNames.TypeName;
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17866369
I think you accepted the wrong answer then.

You need to ask in the Community support forumfor the Q to be re-opened .

Pete
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17867494
No worries Pete, confusion cleared. Ive got a clear head now
0
 
LVL 17

Expert Comment

by:Arji
ID: 17871950
Just a comment.  Seems to me you could have used the CLng() or CInt() functions to convert the text into an integer. That is a better way to insure the number is correctly converted to the proper datatype.  Val() doesn't really do that:

CLng(NZ(QrySumCountByType.CountOfRecordLoan,0))

OR

CInt(NZ(QrySumCountByType.CountOfRecordLoan,0))

The conversion functions are very handy for doing stuff like that: CDec, CStr, CInt, CLng, etc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question