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

Excel output from Access VBA

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
ajwellman
Asked:
ajwellman
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
ajwellmanAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
how are you passing the info to Excel?  post your codes
0
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!

 
Rey Obrero (Capricorn1)Commented:
or try
objXLS.Cells(1, 8).NumberFormat = "0"
0
 
Gustav BrockCIOCommented:
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
 
Helen FeddemaCommented:
Also, make sure the column is wide enough to display the number (whether it is an actual number or text)
0
 
ajwellmanAuthor Commented:
Thanks Gustav,
The oldest method worked great.  
Thanks again for your quick response.
Art.
0
 
ajwellmanAuthor Commented:
Great help.  Thanks
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now