Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    CRLF (Office 2007)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CRLF (Office 2007)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: CRLF (Office 2007)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CRLF (Office 2007)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: CRLF (Office 2007)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •