Solved

Truncation of memo fields exported to Excel

Posted on 2007-04-04
17
696 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now