Solved

Export memo field with more than 255 chars to Excel

Posted on 2003-12-02
15
6,871 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
[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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

615 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