We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Convert Numbers Stored as Text in excell with vb

osama120
osama120 asked
on
Medium Priority
985 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

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

Author

Commented:
is there any way on the excell level
CERTIFIED EXPERT
Top Expert 2006

Commented:
in Excel

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

then uncheck "Numbers stored as Text"


does that work?
 
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

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


CERTIFIED EXPERT

Commented:
Please post the sql for QrySumCountByTypeForm.

Pete

Author

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;
CERTIFIED EXPERT
Top Expert 2006

Commented:
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;
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
thank u so match it work
CERTIFIED EXPERT
Top Expert 2006

Commented:
Im confused, what did u to get it to work?
Did u perform a make table query?

Author

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;
CERTIFIED EXPERT

Commented:
I think you accepted the wrong answer then.

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

Pete
CERTIFIED EXPERT
Top Expert 2006

Commented:
No worries Pete, confusion cleared. Ive got a clear head now

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.