Exporting to Excel changes the format of a text field. Why?

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

Guest

I use the "Output To" function in code to export a table to excel, but it
changes the format of one field. The field contains eg "journal to 12345". In
Excel it only shows the number, unless I increase the column width. It won't
show any of the text until the column is big enough to fit the whole lot.
When I do it maually by clicking on the table, and clicking export, and
ticking the Save formatted check box, it does it fine.
Access help says that running it from code is the same as doing it manually.
But its not. Any ideas??
this is the output code: DoCmd.OutputTo acOutputTable, "journals",
acFormatXLS, , -1
 
It sounds as if the manual "save formatted" option causes Excel to
auto-adjust the column widths, while OutputTo doesn't. The way you
describe the appearance of the cell at first and as you widen it makes
me think that Excel's "Wrap Text" attribute is turned on, and that you
are seeing the last line of the wrapped text. Check this by increasing
the row height.

I've no idea what might be causing this difference or how to control it.
Have you tried using TransferSpreadsheet instead of OutputTo? If that
doesn't avoid the problem, you can of course format the worksheet any
way you like by automating Excel after exporting the data.
 
Back
Top