Export memo field with more than 255 chars to Excel


I have a function where a query is exported by a user but one of the fields is a memo field and only the first 255 chars are making it into the Excel document. I am just using the standard macro provided by Access to export the query in the subform I am using. They then save the document under a name they choose.

Is there any way to get the entire contents of that memo field in there?
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.

jadedataMS Access Systems CreatorCommented:
Hey sph3rion!

  Sorry bubba thats as good as its going to get unless you splice the memo contents out into separate excel columns.


the question is, what the target is.
How would you present the data in the Excel-Sheet, if you where going to key them in manually ? A Excel cell cannot hold more, than 255 characters - except you type it in a comment field - do you want that ?
so what is the final taget ?
what can be done: put the text in cosecutive cells in portions of 255, as long as there is a tablestructure (e.g. make this "field" 3 excel columns which can hold a max. 255*3 characters)

Regards, Franz
> I have a function where a query is exported by a user ...
Can you explain this a bit more in detail. Maybe poste some code of your function ...

I think your memo field is somewhere converted to a Text Field ...

If you export manualy (Database Window -> Right Mouse Click -> Export ...) a table with Memo-Fields (holding more then 255 characters) to Excel, it just works fine ...

Did the test with a memo-field holding more then 1000 charachters, and they were all exported to Excel ...

So, give us some more details ...

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

What version of Access/Excel you're using?
sph3rionAuthor Commented:
Using 2000, Access and Excel

wsteegmans: I can also export it using that manual way and get all the characters... odd

My query populates a subform and I need to export the subform to retain some formatting... the subform will display all the characters, but perhaps somewhere along the way Access loses them because of the export.

franz: My target is just a way for the user to email a set of data to someone and they would like to do it with Excel and in the format that has been set up.

Strangely you can copy the text from the subform directly ONLY if you highlight just the text and NOT the field (like when you let the "+" symbol highlight it). If you copy the field, only 255 chars get pasted into Excel, if you copy the text, it all makes it in there.

Could there be some formatting on the subform text boxes that gets applied in some strange way? I am representing the data in the subform in a DATASHEET view for formatting purposes.

Thanks again guys
Hi wsteegmans

you are right, the 255 character restriction in excel is history
although it is not a good idea to load the cells so heavily

so the loss has to do with some Stringfield in the process

Regards, Franz
sph3rionAuthor Commented:
The code doesn't seem to be available for the export macro, it's an "Output to" macro that exports the subform object with the output format as: Microsoft Excel
How do you export the data ? DoCmd.SendObject ....,acFormatXLS,.....

maybe acFormatXLS can only hold the 255 chrs per field, as this was a resrtiction of older excel-format

regards, Franz
sph3rionAuthor Commented:
It's a standard macro available in Access, but you probably were typing when i posted my last comment :)
another possible reason:

in the query you shoul use teh fieldnames and the meofield as last one

what may result in cuted memofield

SELECT * FROM mytable
SELECT filed1,memeofield,field2 FROM mytable

what should work

SELECT field1,field2,memofield FROM mytable

hope this was it

Regards, Franz
sph3rionAuthor Commented:
The query is alright, it's the subform object that doesn't export correctly, granted it runs the query again when you export so I will try that.
I don't think we must stay using the Output To macro. I scanned some newsgroups, ... all the 255 char problems start when using this macro.

So, can't we use something else? If you use the macro TransferSpreadsheet, you can export a Table or a Query. So, make for example an extra query, with criteria linking to the fields on your main- or subform. So, when you execute the macro TransferSpreadSheet with this new query, only the records will be exported reflecting the data on your form.

And, because we're using TransferSpreadSheet, all characters are exported (no 255 characters limit).

Some info from the Microsoft Site (more about exporting reports to Excel, but same problem).
ACC2000: Memo Field Truncated When Report Is Output to Excel


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
sph3rionAuthor Commented:
Well this is unfortunate, I created the function to help them avoid an extra step of running a macro after they pasted in the info to an excel doc.

Since exporting the subform retains the formatting IE: field width and ht. it pretty much did the same thing the macro did except without the extra step.

I could set up an Excel doc with the fields already formatted and TransferSpreadsheet off of the query.... but it would require me to set this up on each user's machine because of file paths etc.....


going to leave this one overnight as it's very important, see you all in the morning!

jadedataMS Access Systems CreatorCommented:
If you are familiar with CopyFromRecordset you may get better results with that (i know I have)

  open an instance of Excel
  Open a workbook
  set a worksheet
  iterate thru the field names for the column in the worksheet (presume row 1 for this purpose)
  open a query as a recordset
  set RS = db.openrecordset("queryname")
  worksheet.range("A2").copyfromrecordset RS

  This may allow you to post more of the memo than the outputto is allowing.
Hi all.

In MS Access application macro sends email (performs an Action SendObject) with the message from a Forms TextBox object called txtMessage. A Control Source of this txtMessage is a Memo field. Text entered into that memo field is way longer then 255 characters. The problem is that the macro sends only 255 characters of the Message. Is there a way to email everything written in memo field?
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
Microsoft Access

From novice to tech pro — start learning today.