pressclick
New member
- Local time
- Yesterday, 21:22
- Joined
- Jan 10, 2015
- Messages
- 1
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
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