Report Export to Excel

[David Jaco]

I am trying to export a report in Access 2003 to Excel. Every record that has
a number and an 'A' at the end, changes to a number in Excel. For example

In Access In Excel
2A .083333
3A .125
4B 4B

Does anybody know what could be causing this and what I can change to have
the number and letter show up in Excel?

 

[Jen]

heh. that's a good one. Excel thinks those values are dates. I'll have to
look at it some more to see what can be done about it.

 

[John Nurick]

Hi David,

It happens because Excel - always eager to help - assumes that when you
say "3A" you mean "3:00 AM on 1 January 1900" - which in Excel's
date/time system is the number 0.125.

I don't know an easy way round it. One approach that does work is to
modify your report or query so it prefixes the field with an apostrophe,
e.g. by using something like this as the ControlSource of the textbox on
the report:
="'" & [FieldName]

This will leave you with
'3A
and so on in the report, and when you export it to Excel you will see
'3A
in the cell.

If you select the column (or the whole sheet) in Excel and run this
Excel macro

Public Sub RegisterApostrophes()
Dim C As Excel.Range

For Each C in Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

the apostrophes will disappear from the worksheet view, though they will
still be visible in the formula bar and during in-cell editing.

There may be other solutions: a search of Excel forums should find
something.



On Fri, 24 Feb 2006 08:23:28 -0800, "David Jaco" <David

 

[fangliang]

jisuanji test