Link to home
Start Free TrialLog in
Avatar of TonyWootton
TonyWootton

asked on

Truncation of memo fields exported to Excel

If a line like

DoCmd.OutputTo acOutputQuery, "qryUserConflicts", acFormatXLS

is used to create an Excel spreadsheet from an Access query, any memo fields in the query longer than about 254 characters will be truncated to that length. This is a known problem mentioned in the Microsoft KB (see http://support.microsoft.com/kb/208801). I could get round it by using automation to populate the spreadsheet programmatically, but this would be a last resort, given that the above statement is so much simpler and convenient to use, quite apart from the problems that automation presents when attempting to open and close more than a single sheet in a single Access session. Does anyone know a convenient flavour of the above statement that would correct the problem but retain the relative simplicity of the single statement approach. Incidentally, I am presently working with Access2000 and Excel2000.

Thanks, Tony
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

Mod your query then...select lengths of 254 into separate columns prior to exporting. Once in Excel, you can do what you want with them.
Avatar of TonyWootton
TonyWootton

ASKER

Hi jefftwilley

Yes, I appreciate that this could be done (per the KB article I referred to) but I was really looking for a solution from the Access VB end, rather than modifying the query to partition the string (when I have no prior idea of how long the memo field could be).

Regards, Tony
if your goal is to keep the output command as it is, then modifying the query is really your only option.

You could create a parsing routine, or open a recordset of your query, but from code, you either output as, or you open and fill your excel file. You have another option and that's to export as delimited width, or CSV file...excel opens that up just fine.
Just my thoughts.
J
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Possibly you could use the Split() function ... just a wild, quick quess.

mx
Hi jefftwilley,
Thanks for your second response. Although I didn't have keeping the output command as a goal, I did want to keep a simple and brief piece of coding, partly because I will probably want to use the same technique elsewhere. Your CSV option sounds promising (I only wish I'd though of it first!); I need to check it. I will do that tomorrow morning and get back to you.

Thanks, Tony
Hi DatabaseMX,

I have to confess that I can't remember the last time I used the split function. I'll check that out as well and get back to you.

Thanks, Tony
Tony,
If you want to use that syntax, then you are limited by its restrictions. I am puzzled by your statement about  "the problems that automation presents when attempting to open and close more than a single sheet in a single Access session" - I am not aware of any such problems, so can you elaborate?
Regards,
Rory
DoCmd.TransferText acExportDelim, , "queryname", "c:/test.xls", TRUE, ""

this works fine. no truncation.
Hi jefftwilley
I was really hopeful that your suggestion would do the trick, but there appears to be a problem with it. I found that the output file is a mandatory specification and that the extension cannot be XLS or I get an error 3027. With CSV, the output file is created as it should be, but the memo text field is still truncated.
I wanted to avoid using automation, but that presently looks like a looming solution. If you've any bright idea about why this didn't work as it should, I'd be grateful.
Regards, Tony
Tony,
Did you try DoCmd.TransferSpreadsheet using acSpreadsheetTypeExcel9 as the type argument?
Regards,
Rory
That's interesting Tony...I was able to export using transfertext without Truncation. I'm using A2000 XPHome Addition. I'll test more when I get to work.
Hi Rorya,
I've just tried the acSpreadsheetTypeExcel9 argument, but it doesn't have the desired effect.
The automation problem I mentioned earlier concerns an attempt to create a second or subsequent spreadsheet after the first has been created and closed. It appears that it doesn't quite close properly.
If I get a moment, I'll try and let you know more.
Regards, Tony
Tony,
Thanks for letting me know - as I said automation shouldn't be a problem as long as you are destroying all references to the Excel objects when the code finishes and qualifying all the Excel objects properly when referencing them; otherwise, you can be left with invisible instances of Excel running.
Regards,
Rory
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin