Convert Numbers Stored as Text in excell with vb

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
osama120Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
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
osama120Author Commented:
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
osama120Author Commented:
is there any way on the excell level
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rockiroadsCommented:
in Excel

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

then uncheck "Numbers stored as Text"


does that work?
 
0
peter57rCommented:
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
rockiroadsCommented:
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
osama120Author Commented:
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
peter57rCommented:
Please post the sql for QrySumCountByTypeForm.

Pete
0
osama120Author Commented:
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
rockiroadsCommented:
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
peter57rCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
osama120Author Commented:
thank u so match it work
0
rockiroadsCommented:
Im confused, what did u to get it to work?
Did u perform a make table query?
0
osama120Author Commented:
 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
peter57rCommented:
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
rockiroadsCommented:
No worries Pete, confusion cleared. Ive got a clear head now
0
ArjiCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.