Results 1 to 8 of 8
  1. #1
    Yarbz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    13

    Export 2013 Access Report into Word where Paragraph lines stay Connected to pevious line.

    Greetings all,


    I have been asked to write a report in 2013 Access that fills in from a form entry. In the report there are several paragraphs that are of a good length, and vary in size.
    They then are exported into MS Word. No problem thus far, but. . .
    When the .rtf is pulled up in Word, each line is separated from the previous line, and it is a hassle to reconnect each line, plus speed is why this is being created, and connecting all lines slows the process down significantly.

    Question: Is there a macro in Access (Or Word) that would keep the paragraphs together as one continuous typing?
    A friend suggested this in Word, but it doesn't seem to help much.

    Code:
     
    Sub FixParagraph()
    '
    ' FixParagraph Macro
    '
    '
        Dim selectedText As String
        Dim textLength As Integer
    
        selectedText = Selection.Text
    
        ' If no text is selected, this prevents this subroutine from typing another
        ' copy of the character following the cursor into the document
        If Len(selectedText) <= 1 Then
            Exit Sub
        End If
    
        ' Replace all carriage returns and line feeds in the selected text with spaces
        selectedText = Replace(selectedText, vbCr, " ")
        selectedText = Replace(selectedText, vbLf, " ")
    
        ' Get rid of repeated spaces
        Do
            textLength = Len(selectedText)
            selectedText = Replace(selectedText, "  ", " ")
        Loop While textLength <> Len(selectedText)
    
        ' Replace the selected text in the document with the modified text
        Selection.TypeText (selectedText)
    
    End Sub
    Any suggestions Please!
    Yarbs

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yarbz,

    This may be one of those situations where an example would help clarify the requirements.
    A sample of
    -your form,
    -your report data and
    -your required report in word
    would be useful.

    Good luck.

  3. #3
    Yarbz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    13
    Greetings Orange,
    Because of HIPAA, I cant share much.
    The first paragraph code is like this:
    Code:
    =[MrMsLast] & " was admitted to XXX for trial competence restoration pursuant to Article " & [Commitment Type] & " of the Texas Code of Criminal Procedure in connection with felony charges pending in the " & [Court County] & ", Tx. Following " & [hisher] & " present course of treatment at XXX " & [heshe] & " was referred for evaluation of his trial competence by " & [Attending Psychiatrist] & ", " & [hisher] & " attending psychiatrist. "
    and it comes out like this in .RFT:
    Mr. XXX was admitted to XXX for trial competence restoration pursuant to
    Article 46B.102 of the Texas Code of Criminal Procedure in connection with felony charges
    pending in the XXX, XXX County, Tx. Following his present course of treatment
    at XXX he was referred for evaluation of his trial competence by
    XXX, MD, his attending psychiatrist.

    But should be like this:
    Mr. XXX was admitted to XXX for trial competence restoration pursuant to Article 46B.102 of the Texas Code of Criminal Procedure in connection with felony charges pending in the XXX, XXX County, Tx. Following his present course of treatment at XXX he was referred for evaluation of his trial competence by XXX, MD, his attending psychiatrist.

    Each Line in the paragraph should be attached to the previous line, not stopping in the middle of a sentence to create a new line.
    Hope this helps.
    Yarbz

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? I have seen posts/articles where Access is not considered HIPAA compliant.

    I took your .rft text and put it into Notepad++, then asked it to remove EOL (end of line) characters. The result was text as your "should be".

    My first approach would be to replace VbCrLF with a space.
    Your "short" lines may be impacted by a width setting.

    Good luck.

  5. #5
    Yarbz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    13
    Greetings again Orange,
    Forgiver me, but I'm self taught and should be considered a newbie.

    You said, "My first approach would be to replace VbCrLF with a space."
    What is that, and how do I do it?

    Also I agree, the line breaks are at page width, where ever the next line falls, that where the new line is placed in.
    What type of macro or coding can I place in the replace the line break?
    Examples please!

    Yarbz

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have no experience with Word vba.
    For clarity, the code you showed in you earlier post (you called it .RFT) - was that what you see in Word?
    If so then, my thought is that your page width in word is causing this "short line". Try increasing the size of the page width and see if things change. You could try reducing the margins in the word file.

    Re: VbCrLF vbCrLf constant in vba ---carriage return line feed End of line characters

  7. #7
    Yarbz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    13
    In Access, when you export to Word, it creates a Rich Text Format document (.rtf).
    Usually .rtf files are opened in WordPad or NotePad. but you can export it to have the document extension to be .doc, making the Word program open instead.
    The formatting is still rich text formatting and the Document needs to be re-saved as a Word Document to make it work right.

    I've even exported it into .pdf, or adobe acrobat, which Access has, but I run into the same with it too.
    Each line is separate from the previous line.

    So I'll start asking in a Word forum about vbCrLf commands.
    thanks!
    Yarbz

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried wrapping the individual "paragraphs" or the concatenated ones with Replace like this:=Replace([MrMsLast] & " was admitted to XXX for trial competence restoration pursuant to Article............",vbcrlf,CHR(32))?

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Word report - insert paragraph after table
    By DevAccess in forum Programming
    Replies: 0
    Last Post: 06-30-2016, 09:36 AM
  2. Replies: 2
    Last Post: 04-08-2016, 12:24 PM
  3. Replies: 9
    Last Post: 09-18-2015, 09:02 PM
  4. Replies: 3
    Last Post: 04-29-2015, 01:35 PM
  5. export access report to word
    By afshin in forum Access
    Replies: 6
    Last Post: 01-15-2014, 12:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums