Export to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to export a report from Access 2000 to Excel 2000. In access a
field contains numbers such as 4.1.7 but when I export the report Excel
changes that field to .16744. How do I keep the same format in Access. I
tried changing the field from text to number but I have text in some cases. I
can change the field in Excel but other people need to use this and may not
know how. Please help.
 
try exporting as a csv file with delimiters, to force things as text
correctly, with an export spec file. I get similar problems the other way.
Access thinks data is long, say based no the first few rows, when it is
really double, and you dont get the field editor in the wizard. Save the
excel to a csv file, and then you can write import specs to suit. sorry this
is a bit garbled, hope it resolves your problem
 
Hi,

This is happening because Excel is interpreting 4.1.7 as a date/time
value (04:01:07). I'm not absolutely certain of the details, but as far
as I know your choices are

1) if possible, export the table or query and not the report. This
normally flags values in text fields with an apostrophe ', which forces
Excel to treat them as text (the apostrophes are exported to Excel but
do not appear in the worksheet, only in the formula bar).

2) modify the report (or the query it is based on) to insert an
apostrophe before the data in this column, e.g. with a calculated field
in the query:
fMyField: "'"&[MyField]
This ensures that Excel treats the values as text, but for reasons
unknown if you then export the report to Excel the apostrophes are not
hidden as in (1) above. To hide them you must either re-enter each field
(double-click on it and press Enter) or run a bit of Excel VBA code like
this:

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

3) Continue the way you're doing it now.
 
Back
Top