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.