Solved

Export memo field with more than 255 chars to Excel

Posted on 2003-12-02
15
6,806 Views
Last Modified: 2008-08-14
Hello,

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?
0
Comment
Question by:sph3rion
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9862002
Hey sph3rion!

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


regards
Jack
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9862162
Hi,

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
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9862228
> 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 ...

Regards!
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9862245
What version of Access/Excel you're using?
0
 
LVL 1

Author Comment

by:sph3rion
ID: 9862365
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
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9862417
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
0
 
LVL 1

Author Comment

by:sph3rion
ID: 9862478
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
0
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.

 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9862504
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
0
 
LVL 1

Author Comment

by:sph3rion
ID: 9862522
It's a standard macro available in Access, but you probably were typing when i posted my last comment :)
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9862819
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
0
 
LVL 1

Author Comment

by:sph3rion
ID: 9862895
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.
0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 500 total points
ID: 9863001
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
http://support.microsoft.com/default.aspx?scid=kb;EN-US;208801

Regards
0
 
LVL 1

Author Comment

by:sph3rion
ID: 9863070
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.....

hmm...

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

mike
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9864429
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.
0
 

Expert Comment

by:salika
ID: 23442174
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?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

15 Experts available now in Live!

Get 1:1 Help Now