Solved

Excel output from Access VBA

Posted on 2014-12-19
9
194 Views
Last Modified: 2014-12-22
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.
0
Comment
Question by:ajwellman
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40509325
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
 

Author Comment

by:ajwellman
ID: 40509371
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40509381
how are you passing the info to Excel?  post your codes
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40509400
or try
objXLS.Cells(1, 8).NumberFormat = "0"
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40510651
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40511862
Also, make sure the column is wide enough to display the number (whether it is an actual number or text)
0
 

Author Comment

by:ajwellman
ID: 40512952
Thanks Gustav,
The oldest method worked great.  
Thanks again for your quick response.
Art.
0
 

Author Closing Comment

by:ajwellman
ID: 40512957
Great help.  Thanks
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40512971
You are welcome!

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

17 Experts available now in Live!

Get 1:1 Help Now