Solved

Convert Numbers Stored as Text in excell with vb

Posted on 2006-11-02
17
928 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

863 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

25 Experts available now in Live!

Get 1:1 Help Now