It’s been a busy week and I’ve been working hard. I think I’ll reward myself by writing a blog post for people I’ve never met. Here we go.
Let’s talk about getting Access Memo Fields into Excel.
So you’re there you are, happily copying data from this Access table to Excel. Notice the comment length here (big stuff).
Then you figure out that Access is cutting off your ‘comments’ field at 255 characters.
This is because your ‘comments’ field is actually a Memo data type. In a field designated as a Memo field, you can enter as many characters as you like (no 255 character limit). Unfortunately, copying and pasting Memo fields into Excel will result in data that is cut off. I know…Excel can handle well over 30,000 characters in a cell. The problem is when you copy and paste from Access, Access still exports only 255 characters for backwards compatibility purposes.
So what do you do?
(a) Start copying and pasting individual comments 255 characters at a time.
(b) Parse your comments into separate 255-character fields, then concatenate them in Excel.
(c) Get a job at Barnes & Noble to spend your days alphabetizing books.
The answer is (c).
But just in case you can’t afford to quit your job, here are a couple of methods you can use to get Access Memo fields into Excel.
Method 1: Use the Export with Formatting Option
Right click on your table and choose to export it.
When choosing an output, be sure to select the ‘Export data with formatting and layout’ option.
Method 2: Use a TransferSpreadsheet Macro
You can also create a Macro to use the TransferSpreadsheet action. Using this action will bypass Access’ tendency to cut data off to 255 characters.
As you can see, you’ll have to identify the source table, and the destination path.
There you have it. Now if you’ll excuse me, I’ve got to go get ready for my Barnes & Noble interview.
RELATED STUFF
- Using Access to Combine Multiple Excel Files: Method 1
- Sending Data to Excel using Access SQL
- Using Access to Combine Multiple Excel Files: Method 2
- Filter on Selection in Excel
- Sending Variable Access Data To An Excel Range
- Running an Access Parameter Query from Excel
- Documenting Access Queries in Excel
- Easily Move Access Query Results to Excel
- The Benefits of a Good Technical Editor
- Clearing Access ImportError Tables
This is a PITA when transferring data programmatically aswell, although the character limit varies with the method used.
IIRC ADO.NET throws an exception if you try to bring across a field of more than 911 characters (how’s that for an arbitrary number?). This was causing me real pain until I found the MSDN article and realised what was causing it.
Luckily for me I had control over the data entry for the system and could limit the input to 911 characters which was not really a limiting factor in the system.
You import spam comments into Access? Is that what you call working hard?
JP: That’s what we in the business call Greekin. I used a website that auto-generates Greekin.
I prefer to pull the data in from Excel (database query), which also retains the memo fields (for as much as Excel can handle, that is).
Jan Karel: Good point!
If you want to just get the memo data to Excel quick and easy, just copy all the data you want and paste to the worksheet using Paste Special:Text. A lot of times I can manipulate memo quickly and paste them back to Access with no fuss.