Export ot excel losing characters (97)

Topic

New Reply

Howdy all,

I am having a problem when I export from access to an excel file. When the export occurs Excel is only receiving the first 255 characters and then everything else beconmes truncated in excel. I have access files with 300+ words but on the export i lose anything over the 255th character. What can i do to export ALL of my data to excel. Thank you very much.

Ed

Reply | Quote

Replies

I started to tell you that Excel 97 will only store 255 characters in a cell, but that turns out not to be the case. The limit is the number of characters that you can display in a single cell, which is 255. But you can apparently store up to 32000 characters in a cell – I tried up to 500 and it worked.

So I suspect it is the export routine that is arbitrarily limiting cells to 255 characters. That I didn’t test, but I seem to remember seeing this same problem explored in other posts on this forum. A couple of ideas to try:
.

• If your fields are only slightly over 255 characters (i.e. 350 or 400), then take the left most 255 and put them in one query field, and the next 255 or less and put them in a second query field using the Left and Mid functions
• Try exporting your data to a tab-delimited file, and then open that in Excel
  [/list]Hopefully one of those will get what you need in Excel.

Reply | Quote

You’ll run into problems anyhow with Excel 97, even if you get a larger number of characters in it. You can do it, but it you open the spreadsheet and go into that larger field, it will truncate the entire column to hold 255 max. At least, that’s the experience I had with it under those circumstances. It will hold a larger number of characters, but you can’t manipulate the spreadsheet without losing the additional data. I would recommend you try Wendell’s approach.

Reply | Quote

Would it be possible to do this:

Run my macro and then insert coding that will cut and paste certain columns to excel and thus overwrite the imported truncated data. You can copy and paste from access to excel without losing data so in theory this might work, correct? Could someone show me how?

Reply | Quote

If you want to keep all the data in Excel, you’re going to have to break it up into 255 character blocks anyhow, so why not do it the easy way, at the query stage?

Reply | Quote

I would love to do that but let me explain how this monster ive created works.

You enter data into a template on excel. This template is formatted to be exported perfectly to an access database which is constantly updated. Occasionally, someone will run a query based on a specific job# and all the data will come up, it is then exported to another excel template that allows the user to edit previous information. We do not allow edits to be done to the database itself because format restrictions are alot harder in access than in excel, and we use drop down list etc from excel. So when someone wants to edit a job with 10 rows and 20 columns they have to do it from excel. If the everything is broken up into 255 blocks then i will need to create sn insane amount of extra columns to store potential data. sometimes one field will have a few thousand characters. i do not want alot of empty columns sitting around for all the jobs that are brief. That’s why i think the copy and paste will be better because it will keep the size down and prevent me from going back and editing the templates and column titles in order ot handle the import/exports properly. What are your thoughts, does my suggestion make sense, i take it you know alot more on the subject than I. Thank you.

Ed

Reply | Quote

I’m afraid you’re right – you’ve created a monster. What I don’t understand is why Excel is better at formatting data than Access?

Access has much stronger data verification and validity checks than Excel – I got my initial MCP for Excel nearly 10 years ago, and work with it regularly – but there is no better Office tool for capturing data and validating it than Access. In addition, Excel has a number of problems in doing what you suggest over and above the 255 character problem. For one thing, there is nothing to prevent you from putting something other than the drop-down list in Excel unless you write lots of VBA to restrict the cell contents. Another issue is Excel’s tendency to occasionally go corrupt or crash – any data being entered in that situation is gone forever – while Access saves data on the fly as you enter it. Also, Access is multi-user, and will deal with situations where 2 or more people try to edit the same record – using Excel in the manner you describe will let 2 people edit the record at the same time. Finally, Access is relational where Excel is not, and with a good table design you will have very few empty columns.

I’ll answer you copy/paste question on the other post. Hope this is useful to you.

Reply | Quote

Try changing data types from [text] to [memo] in the fields in access, or excel?

Reply | Quote

They already are memo fields

Reply | Quote