Solved

Excel output from Access VBA

Posted on 2014-12-19
9
197 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40509381
how are you passing the info to Excel?  post your codes
0
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.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40509400
or try
objXLS.Cells(1, 8).NumberFormat = "0"
0
 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 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