MS OfficeLinks (from Access report to Excel)
 

I posted this on the Access Discussion Group board but I think it is really a
Excel issue. Please keep in mind that I do not want the user to have to
reformat the cells as that may be above their abilities.

I have a MS Access report that I want the user to be able to export to Excel
using the MS OfficeLinks button on the report tool bar. When I export the
data I have two issues occurring:

1) Item data (text in access) 6610014887408 is being displayed as scientific
notation

2) Item data (text in access) 005656406 is having the zeros removed from the
front

Is there a way around this so the user does not have to manually reformat
the Excel spreadsheet? When you export using OfficeLinks it automatically
creates a new spreadsheet so setting the cell formatting in advance is not an
option.

MS OfficeLinks (from Access report to Excel)
 

One thought: if you put an apostrophe to the left of a string of numbers, XL
treats it as text. Therefore, modify the query in Access to include an
apostrophe to the left of the numbers. Without seeing your SQL, it's hard to
know exactly how you would do that, but I believe Access has a function
similar to XL's CONCATENATE function which can join text strings together in
query output.

Dave
--
Brevity is the soul of wit.


"ES" wrote:

I posted this on the Access Discussion Group board but I think it is really a
Excel issue. Please keep in mind that I do not want the user to have to
reformat the cells as that may be above their abilities.

I have a MS Access report that I want the user to be able to export to Excel
using the MS OfficeLinks button on the report tool bar. When I export the
data I have two issues occurring:

1) Item data (text in access) 6610014887408 is being displayed as scientific
notation

2) Item data (text in access) 005656406 is having the zeros removed from the
front

Is there a way around this so the user does not have to manually reformat
the Excel spreadsheet? When you export using OfficeLinks it automatically
creates a new spreadsheet so setting the cell formatting in advance is not an
option.

MS OfficeLinks (from Access report to Excel)
 

That works in keeping the numbers as text but Excel is showing the apostrophe
in the cell and reformatting in Excel to text does not remove it.