[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • 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 BurtonCommented:
Why not have, instead of your second line, this:

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

That ' should force it as text.
0
 
NorieCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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