OutputTo or TransferSpreadsheet

[lljo40]

I need to export a query to Excel (using Office XP).

I like using OutputTo, because it does a good job of formatting the
output, BUT it truncates Memo fields to 255 characters.

So I tried TransferSpreadsheet, and it does export all the Memo data,
but doesn't do anything to format it (which means the user has to do
some cleanup).

Of course, right now, I'm going with TransferSpreadsheet, since it gets
all the data, but it sure would be nice if OutputTo could be made to
export using the right version of Excel (I assume it's still using an
older Excel format which allowed only 255 characters).
Any ideas?

TIA,
Larry

 

[Van T. Dinh]

IIRC, acFormatXLS used in OutputTo means Excel version 5-7.

If you look into the OutputTo Macro action you can specify the version of
Excel but (unfortunately) I am not sure the equivalent acFormat constant so
that you can specify for the VBA OutputTo method.

If you really want it, you can create a Macro using the OutputTo Macro
action. Note that support for Macros may not be available for future
version of Access (but Microsoft have been saying this since A97 and Macros
are still in A2003).

 

[lljo40]

I tried coverting Macros to code, but that doesn't work either. If you
convert the Output format, to VBA, it creates the following:
- Microsoft Excel (*.xls) = MicrosoftExcelBiff8(*.xls)
- Microsoft Excel 5-7 (*.xls) = MicrosoftExcelBiff5(*.xls)
- Microsoft Excel 97-2002 (*.xls) = MicrosoftExcelBiff8(*.xls)

Using the code for 5-7, didn't work and since the code for 97-2002 is
the same for earlier versions, it's not working either. I even tried
using "Microsoft Excel 97-2002 (*.xls)" this way, but that didn't work
either.

I even tried using this as a macro, without converting it to code, and
it doesn't work that way either.

Any other ideas?

 

[Van T. Dinh]

Does the Query show more than 255 characters when you open the Query in
Access?

 

[lljo40]

Yes, the query is fine. I checked to make sure at the query level that
it had all the data, plus the TransferSpreadsheet method does output
the entire field.

 

[Van T. Dinh]

Personally, I would normally use Excel Automation to create a new Excel XLS
based on a customised Template to suit my formatting requiredment rather
than relying on OutputTo.

The CopyFromRecordset Method of the (Excel) Range object can copy a
Recordset to the cells in the Range.

There are a number of articles on Microsoft Support Web site that have
details on Excel Automation from Access.

 

[lljo40]

I use a lot of Excel Automation, for difficult things especially, but
since this was such an "easy" requirement, I thought I would try to use
the built-in methods.

Since this doesn't work in Office XP, do you know if this functionality
has been fixed in Office 2003? If so, I may just tell the user to go
with what I have, until the company upgrades to 2003 this summer.

BTW, thanks for trying to help.

 

[Van T. Dinh]

Sorry, I am mainly using A2002 at present.