Excel output from Access VBA

I am creating a spread sheet from Access using VBA.  I have a serial field that should be displayed as text all the time but am having a problem with it. The serial number field contains alpha and numeric data.  When the serial number happens to be a large number (eg: 5305588100043) it displays on the spreadsheet as 5.30559E+12.  Within the VBA code to create the spreadsheet, I have the serial number cell set to objXLS.Cells(1, 8).NumberFormat = "@".  
Is there any other way to force this cell to always display full text regardless of what data is in that field?
Thanks for your help
Art.
ajwellmanAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You can use the very oldest method to force a string in a spreadsheet:

Select
    Chr(39) & CStr([SerialNumber]) As SNo,
    <other fields>
From
    tblYourTable

and export this query or use:

    objXLS.Cells(1, 8).Value=Chr(39) & CStr(rst![SerialNumber].Value)

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
try forcing the serial number field to be text, in a query

select cstr([SerialNumber]), f1,f2
from tablex

then export the query

or explicitly fill the cell with
objXLS.Cells(1, 8).value=cstr(rs!serialNumber)
0
 
ajwellmanAuthor Commented:
Thanks for the quick response.

I just tried both of these and it is still showing up as 5.30559E+12 when I open the spreadsheet.
I'm sure it must be something that I am doing, but I sure don't see it.

Art
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Rey Obrero (Capricorn1)Commented:
how are you passing the info to Excel?  post your codes
0
 
Rey Obrero (Capricorn1)Commented:
or try
objXLS.Cells(1, 8).NumberFormat = "0"
0
 
Helen FeddemaCommented:
Also, make sure the column is wide enough to display the number (whether it is an actual number or text)
0
 
ajwellmanAuthor Commented:
Thanks Gustav,
The oldest method worked great.  
Thanks again for your quick response.
Art.
0
 
ajwellmanAuthor Commented:
Great help.  Thanks
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.