Excel Hints

Results 1 to 5 of 5

Thread: Macro to replace <Paragraph_mark> with <Space> (remove line breaks)

  1. #1

    Question Macro to replace <Paragraph_mark> with <Space> (remove line breaks)

    Hello dear VBA gurus, coding geniuses, and dumb newbies (like myself).

    The question I am about to ask is asked many, many times. But I could not found an elegant and good-working solution. The problem is this:
    When you import or simply copy/paste from Adobe PDF (or some other clumsy format) file into MS Word 2007, often the paragraphs are truncated. (Every single line becomes new paragraph.)

    Of course, even a dumb newbie (like myself) can record a macro to replace the Paragraph mark (^p) with <Space>, but this macro is very annoyingly interrupted by the Word_2007 question:
    Word has finished searching the selection. X replacements were made.
    Do you want to search the reminder of the document?
    Yes | No
    And before you suggest the following solution:
    Application.DisplayAlerts = False
    I will tell you - this is not working, because Application.DisplayAlerts = False suppress the dialog box but with the DEFAULT answer (Yes)! And the result is catastrophic replacement in the whole document.

    Here is the code of the first macro I use:
    VB:
    Sub Para_Dupara() 
         ' Para_Join Macro
        Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend 
        Selection.Find.ClearFormatting 
        Selection.Find.Replacement.ClearFormatting 
        With Selection.Find 
            .Text = "^p" 
            .Replacement.Text = " " 
            .Forward = True 
            .Wrap = wdFindAsk 
            .Format = False 
            .MatchCase = False 
            .MatchWholeWord = False 
            .MatchWildcards = False 
            .MatchSoundsLike = False 
            .MatchAllWordForms = False 
        End With 
        Selection.Find.Execute Replace:=wdReplaceAll 
         'HERE WORD ASKS THE ANNOYING QUESTION Yes/No
        Selection.MoveDown Unit:=wdLine, Count:=1 
        Selection.HomeKey Unit:=wdLine 
    End Sub 
    
    
    Formatting tags added by mark007
    Please, please tell me how to bypass the Yes|No dialog box with answer No, in the above macro. (I have to edit ~400 pages truncated paragraphs.)

    The other macro for replacing <paragraph> with <space> is killing the formatting (bold, italic, etc.):
    VB:
    Sub Paragraph_Join() 
         ' Macro to replace <Paragraph character> with <Space>
         ' START FROM THE END OF THE DOCUMENT (Upwards)!
         ' And Turn OFF "Use Smart Paragraph Select"!
        Dim sText As Range 
        Set sText = Selection.Range 
        If Len(sText) = 0 Then 
            MsgBox "Hey stupid, you have no text selected!", vbCritical 
            Exit Sub 
        End If 
        sText = Replace(sText, Chr(13), Chr(32)) 
        Selection.MoveUp Unit:=wdLine, Count:=1 
        Selection.EndKey Unit:=wdLine 
    End Sub 
    
    
    Formatting tags added by mark007
    If you know how to preserve font formatting in the above macro, please tell me.

    Thank you very much for your time, knowledge and patience.
    P.S.
    By the way, the AutoFormat feature of Word 2007 also is not a solution. It works sometimes, but with very crazy PDFs the result of AutoFormat is also crazy.

  2. #2
    Have you tired replacing wdFindAsk with wdFindStop

  3. #3

    Thumbs up Thank you very much!

    Wow! This was fast!
    Mr. Greg, I LOVE YOU! Now I know why NASA’s Mars Polar Lander was destroyed due to bad flight software – because they didn’t have certain Mr. Greg in the software team!

    Replacing wdFindAsk with wdFindStop did the magic! Now the macro is working flawlessly, without dialog box interruption!
    Thank you so much Mr. Greg!
    This was me in the last 3 days -> And now, this is me ->

    Here is the fine working macro:
    VB:
    Sub Para_Join() 
         ' Para_Join Macro
         '
        Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend 
        Selection.Find.ClearFormatting 
        Selection.Find.Replacement.ClearFormatting 
        With Selection.Find 
            .Text = "^p" 
            .Replacement.Text = " " 
            .Forward = True 
            .Wrap = wdFindStop 
            .Format = False 
            .MatchCase = False 
            .MatchWholeWord = False 
            .MatchWildcards = False 
            .MatchSoundsLike = False 
            .MatchAllWordForms = False 
        End With 
        Selection.Find.Execute Replace:=wdReplaceAll 
        Selection.MoveDown Unit:=wdLine, Count:=1 
        Selection.HomeKey Unit:=wdLine 
    End Sub 
    
    
    Formatting tags added by mark007

  4. #4
    That has to be the most enthusiastic appropation offered recently. Thank you and you are welcome.

    It might need tweaked for interantional differences, but you might find this helpful: http://gregmaxey.mvps.org/Clean_Up_Text.htm

  5. #5
    Thank you for the Clean Up Text template. It looks very useful. Will try it.

    I can not mark this thread Solved. (No such option in the Thread Tools) Maybe I don't have enough rank in the forum? If some admin reads this, please, mark this thread Solved.

    I am enthusiastic Mr. Greg, because you really helped me. Try to imagine 400 pages of truncated paragraphs... Living nightmare.

    Thank you again. And God bless you.

Posting Permissions

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