Question Excel->Access->Excel Formatting (1 Viewer)

Question:

I know there are lot of threads, questions, and angst out there about maintaining formatting during Access/Excel importing and exporting operations.

Generally there isn't anyway to maintain formatting during these processes, but I have observed the following:

When I use VBA DAO operations to update Access records from an Excel file that contains a lot of textual information broken into paragraphs with carriage returns, these carriage returns are maintained when I re-export the information via VBA to a new Excel file. HOWEVER, when I view the information in Access, there are no carriage returns - it is all just one big block of text.

So to summarize: Import text with paragraphs created via carriage returns into Access through VBA. Observe that the data in Access has been stripped of the returns and has been converted into a single paragraph. Export data into Excel through VBA and note that the carriage returns are back and are exactly where they were before.

So my hypothesis is that there IS SOME formatting that survives the Excel/Access import/export process at least when executed via coding.

Has anyone else observed this or does anyone have thoughts on this?

My goal would be to write or tweak something in Access that would find this formatting and break the text into paragraphs. You would think there would be a way to step through an Access memo field, detect invisible formatting tags, and use code to insert some Char(10)s or line breaks, but maybe I'm too optimistic.

Thanks!

Example:

Text in Excel (all in one cell):
"AG" - Agriculture
"DI" - Deposit Instruments
"EQ" - Equipment
"FI" - Financial Instruments
"MN" - Mining
"MS" - Misc
"OG" - Oil & Gas Properties
"OT" - OTHER
"RE" - Real Estate
"NA" - Source Not Available
"TM" - Trademarks
"TA" - Trading Assets
"UK" - Unknown
"US" - Unsecured
"OT" - Miscellaneous/Other

Text in Access memo field after import:
"AG" - Agriculture "DI" - Deposit Instruments "EQ" - Equipment "FI" - Financial Instruments "MN" - Mining "MS" - Misc "OG" - Oil & Gas Properties "OT" - OTHER "RE" - Real Estate "NA" - Source Not Available
"TM" - Trademarks "TA" - Trading Assets "UK" - Unknown "US" - Unsecured
"OT" - Miscellaneous/Other

Text in Excel after programmatically exporting through VBA:
"AG" - Agriculture
"DI" - Deposit Instruments
"EQ" - Equipment
"FI" - Financial Instruments
"MN" - Mining
"MS" - Misc
"OG" - Oil & Gas Properties
"OT" - OTHER
"RE" - Real Estate
"NA" - Source Not Available
"TM" - Trademarks
"TA" - Trading Assets
"UK" - Unknown
"US" - Unsecured
"OT" - Miscellaneous/Other

Are you trying to reformat a memo field which would appear in one Excel cell, or such that it would appear in multiple Excel rows, not just one row?

In the former case, have you tried using the Access "Replace" function. That should do what you want.

In the latter case, I'd create a related table with multiple related rows using ADO or DAO to generate the multiple "new" rows.

Find out what characters you have. Using the string functions http://www.techonthenet.com/access/functions/ you should find the location for the "mystery" charactaers and using the function Asc you can get the ascii code for them.

Besides, play with the simple/rich text option for the textbox (in the Properties/Other tab) displaying your data and the memofiled in the table.

This happens because non-printing characters are still ASCII characters and are preserved, even if you can't see them.

If all else fails, you can write a simple function that takes a provided string (such as the contents of a memo field), cycles through it, and strips out any non-printing characters.