• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

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...
0
ajwellman
Asked:
ajwellman
1 Solution
 
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
 
NorieVBA ExpertCommented:
Have you tried setting the NumberFormat before putting the value in the cell?
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
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

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 now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now