CRLF (Office 2007)

Topic

New Reply

Good evening
Access 2007, Word 2007
I am updating an Access memo field with the contents of a Word Table cell.

I would like to preserve the carriage returns from the word table cell.
The action is driven by MS Word.

It seems that MS Word does not include a linefeed as part of the carriage return.

I have tried replacing vbCr with vbCrLF and chr(13) with chr(13) & chr(10). Both failed.

Any hints would be appreciated

Geof

Reply | Quote

Replies

I don’t have Office 2007, but in earlier versions, copying the contents of a table cell into a memo field or text box bound to a memo field works correctly, both line breaks and paragraph breaks are converted to line breaks. It works both interactively and in VBA.

Reply | Quote

Hello Hans
Thank you for the reply. I will investigate the issue in Access 2003.
I had found that
* I could copy & paste into the memo field and preserve the CrLf
* That the CrLf appeared to be preserved when using debug.print to the immediate window in the Word VBA editor.

The following snippet demonstrates my approach to populating t he database. It is part of a routine that loops through identically structured tables in a word document. The left & len functions strip the end of cell, end of row and the end of table markers.

With oRs
.AddNew
!resText = Left(ActiveDocument.Tables(i).Rows(2).Range.Text, Len(ActiveDocument.Tables(i).Rows(2).Range.Text) – 3)
.Update
oRs.Requery

An aside ..
The following snippet seems to highlight the issue with MS Word CrLf.
Create a table and populate a cell with a series of words each on a separate line/paragraph. Leave the Word cursor in the cell.

Run this routine
sub test
sVar = selection.cells(1).range.text
Msgbox(instr(sVar, vbCrLf))
Msgbox(instr(sVar, vbCr))
Msgbox(instr(sVar, vbLf))
End sub

Cheers & thanks
Geof

Off to investigate

Reply | Quote

Ah yes, you hadn’t mentioned ADO or DAO anywhere in your original question. Access knows how to handle multi-paragraph text from Word, ADO and DAO don’t.
The following works OK when I try it in Access 2002 or 2003:

…
Dim strText As String
strText = ActiveDocument.Tables(i).Rows(2).Range.Text
strText = Left(strText, Len(strText) – 3)
!resText = Replace(strText, vbCr, vbCrLf)
…

By the way, if the table has more than one cell, using Rows(2) will include the cell markers of all but the last cell in the field value.

Reply | Quote

Hi Hans
Thanks for the tip.

Further to the mix ..
In access 2007 there is the option of rich text for memo fields. (and I guess text fields)
Rich text is stored as html.

The solution to my problem was to replace vbCr with the html para break
on the way into the database.
If exporting back out I just reversed the replacement.

Thanks again for the help

Geof

Reply | Quote