vb problem reading text from Word tables

RBECKMN
RBECKMN used Ask the Experts™
on
I wrote a program that reads the cells of Word (2007) tables and puts the results into a database.  Currently using Access 2007 memo fields.  Everything is fine except that the carriage returns and line breaks don't carry over into the database.  for some reason they are stripped so the resulting text is one long string.  even a blank line in the text gets stripped out when it goes into the database.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Make sure the "Text Format" property (Not to be confused with the "Format" property) of the memo field is set to "Rich Text"

Can you give us some insight into your "Program" and how it inserts the text into the field?
If setting the Text Format to Rich text does not work, then another option would be to insert the text via SQL Inserts.

Something roughly like this:
CurrentDb.Execute "INSERT INTO Yourtable (YourMemoField) VALUES ('SomeText1' & '<br>' & 'SomeText2')", dbFailOnError

...With your "Program" converting whatever character the cr/lf is in Word, ...to <br> for Rich Text (HTML)

JeffCoachman
Top Expert 2015
Commented:
Word does not records the line breaks, it records only the carriage return with one character only, Chr(13).

Access memo fields record line breaks as the more standard combination of 2 characters, Chr(13) Chr(10).

I would try something like the following:

textInBd = textInWord.Replace(Chr(10), Chr(13) & Chr(10))

Author

Commented:
-- original code that was causing the problem:

TheString = theTable.cell(ThisRow, 1).Range.Text

-- the code now is:

TheString = Replace(theTable.Cell(ThisRow, 1).Range.Text, Chr(13), Chr(13) & Chr(10))

-- followed by:

therecordset(n)=theString, where n is the field number in the recordset

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial