Solved

Convert Numbers Stored as Text in excell with vb

Posted on 2006-11-02
17
924 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now