Solved

Excel output from Access VBA

Posted on 2015-02-20
3
83 Views
Last Modified: 2016-02-10
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
Comment
Question by:ajwellman
3 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40621251
Why not have, instead of your second line, this:

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

That ' should force it as text.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40621265
Have you tried setting the NumberFormat before putting the value in the cell?
0
 

Author Closing Comment

by:ajwellman
ID: 40621614
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now