?
Solved

Truncation of memo fields exported to Excel

Posted on 2007-04-04
17
Medium Priority
?
703 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 500 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

718 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