Transfer Spreadsheet vs. File Export in Access 1

[netrusher]

Below is the code I am using to send a query to Excel. The problem is if a cell is over 255 characters it is truncated. If I go FILE then Export the query it does not truncate the cells in Excel. How can I turn the
File Export to a command button or VBA to the Cells are not truncated in Excel?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryECNPlannedByMonthPIDNew", "c:\my documents\ECNSPIP.xls", True, ""

 

[Remou]

I think you are looking for OutputTo. Are you sure you Jet drivers are up-to-date:

http://support.microsoft.com/kb/239114

?

http://lessthandot.com

 

[netrusher]

Remou,

Thanks for the advice. I have tried output to through a
macro and it truncates the celss in Excell to 255
characters. The only way I have been able to find that will
not truncate the memo fields from access in excel is to go
File Export. I want to automate this and do not know how. I
do not know anything about the Jet drivers. This is a huge
corporation so I am assuming everything is up to date. What
do I check for?

 

[Remou]

I included a link to Microsoft that gives details of what to check for. If it is a huge corporation, it is quite likely that the Jet engine has been overlooked.

http://lessthandot.com

 

[netrusher]

We have version
MSJET40.dll 4.0.9511.0

 

[Remou]

I would have thought that would be fine. I can export large memo fields using TransferSpreadsheet with Access 2000 on Vista or XP. Do any of the usual reasons apply:

http://allenbrowne.com/ser-63.html

? Can you use TransferSpreadsheet successfully with the table itself?

http://lessthandot.com

 

[netrusher]

My query displays the data correctly. It only truncates
when I DoCmd Transfer Spreadsheet or OutputTo. If I Export
it does not truncate. Any other suggestions?

 

[Remou]

You have stated that you have tried this with a query, have you tried transferspreadsheet with the table the query is based on?

http://lessthandot.com