Excel output from Access VBA

I have an Access VBA application that writes to an Excel spreadsheet.  I have a serial number field that is a text field in access and contains alphanumeric data.  There are some that are numeric only and if the number is large, it appears on the spreadsheet as a scientific notation. (eg.  5.30559E+12 instead of 5305588100056).  I have tried everything I can think of to force it to always print just as text.
In my code I have:
strSerialNumber = Nz(!SerialNumber, "")
.....
objXLS.Cells(intRow, 4).Value = RTrim$(strSerialNumber)
objXLS.Cells(intRow, 4).NumberFormat = "@"
....
    objXLS.ActiveWorkbook.SaveAs strfile
Any help if forcing the serial number to always display as text would be appreciated.
Thanks
Art...
ajwellmanAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Have you tried setting the NumberFormat before putting the value in the cell?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Why not have, instead of your second line, this:

objXLS.Cells(intRow, 4).Value = "'" & RTrim$(strSerialNumber)

That ' should force it as text.
0
 
ajwellmanAuthor Commented:
Thanks Norie.   This worked great.  I had tried putting a character in front to force, but couldn't change the serial in any way.
Thanks again for such a quick solution.
Art
0
All Courses

From novice to tech pro — start learning today.