Solved

Truncation of memo fields exported to Excel

Posted on 2007-04-04
17
702 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:TonyWootton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +2
17 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18851873
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
 

Author Comment

by:TonyWootton
ID: 18851919
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18851952
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 75
ID: 18852389
Possibly you could use the Split() function ... just a wild, quick quess.

mx
0
 

Author Comment

by:TonyWootton
ID: 18853537
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
 

Author Comment

by:TonyWootton
ID: 18853552
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18854414
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18854481
DoCmd.TransferText acExportDelim, , "queryname", "c:/test.xls", TRUE, ""

this works fine. no truncation.
0
 

Author Comment

by:TonyWootton
ID: 18856564
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18856614
Tony,
Did you try DoCmd.TransferSpreadsheet using acSpreadsheetTypeExcel9 as the type argument?
Regards,
Rory
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18856866
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
 

Author Comment

by:TonyWootton
ID: 18856949
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18856987
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
ID: 18857087
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 125 total points
ID: 18857185
Hey Tony,
did you give this a try

DoCmd.TransferText acExportDelim, , "queryname", "c:/test.csv", TRUE, ""
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21177274
Forced accept.

Computer101
EE Admin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question