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
TonyWoottonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jefftwilleyCommented:
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.
0
TonyWoottonAuthor Commented:
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
0
jefftwilleyCommented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Possibly you could use the Split() function ... just a wild, quick quess.

mx
0
TonyWoottonAuthor Commented:
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
0
TonyWoottonAuthor Commented:
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
0
Rory ArchibaldCommented:
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
0
jefftwilleyCommented:
DoCmd.TransferText acExportDelim, , "queryname", "c:/test.xls", TRUE, ""

this works fine. no truncation.
0
TonyWoottonAuthor Commented:
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
0
Rory ArchibaldCommented:
Tony,
Did you try DoCmd.TransferSpreadsheet using acSpreadsheetTypeExcel9 as the type argument?
Regards,
Rory
0
jefftwilleyCommented:
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.
0
TonyWoottonAuthor Commented:
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
0
Rory ArchibaldCommented:
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
0
Rory ArchibaldCommented:
Tony,
One other thing - do you have the latest service pack applied? I know there was a memo truncation bug in Access 2000 fixed in SP3 (though I think that was truncating at 127 characters).
Regards,
Rory
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jefftwilleyCommented:
Hey Tony,
did you give this a try

DoCmd.TransferText acExportDelim, , "queryname", "c:/test.csv", TRUE, ""
0
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.