Solved

How can I eliminate Paragraph Marks using VBA?

Posted on 2016-11-11
7
69 Views
Last Modified: 2016-11-11
Basically, I am automating a mail merge document (using MS Access) and saving it as a text file; but it needs to be a "flat file" with no Paragraph Marks, Carriage Returns, or continuous Section Breaks (that mail merge adds when there are multiple records).

I have tried using the following code, which is probably a brute force approach.  It eliminates the extraneous items, but I am still left with some Paragraph Marks (ASCII #266) and one continuous Section Break.

         Selection.Find.ClearFormatting
         Selection.Find.Replacement.ClearFormatting
         Selection.WholeStory
         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.WholeStory
         With Selection.Find
            'remove manual page breaks
            .MatchWildcards = False
            .Text = "^m"
            .Replacement.Text = ""
            .Execute Replace:=wdReplaceAll
         End With
         Selection.Find.Execute
         Selection.Find.Execute Replace:=wdReplaceAll

or is there a way to use?

'         wrdApp.ActiveDocument.Content = Replace(Selection.Text, ChrW$(266), "")

Thanks!
0
Comment
Question by:Access57
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 41883940
Have you tried saving the document as plain text?  Word would automatically remove all non-text elements.  If you can't get the mail-merge to do this automatically, output the documents to a temp folder and at the end of the process, open each and re save as plain text.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 41883941
How about this:

wrdApp.ActiveDocument.Select
wrdApp.Selection.ClearFormatting
0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41883972
Hi,

pls try
Sub macro()

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.WholeStory
For Each Item In Split("^p,^m,^v,^b", ",")
With Selection.Find
    .Text = Item
    .Replacement.Text = ""
    .Forward = True
    .Execute Replace:=wdReplaceAll
End With
Next
End Sub

Open in new window

Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Access57
ID: 41884119
Thank you for everyone's quick response.

1). First response - I am already saving it as FileFormat:=wdFormatText and that is not eliminating the problem.
2). Second response - I am not sure how that would work.
3). Third response looks promising, I have implemented the code but need clarification.

I have added "Dim Item As Object", but confused with the Split and not sure what additional things I need to make "Split("^p,^m,^v,^b", ",")" work.

I have added the code and I am getting Error 424, Object required
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 41884142
Are you trying to get to a specific format that will be used as an import by another application?  If so, you should probably be looking at an Access or Excel solution.
0
 

Author Comment

by:Access57
ID: 41884187
I am automating a business process that sends out a "flat file" using MS Access as a base.  Since the file has data in it, I am creating the document using MS Word Mail Merge operating in background.   Part of the problem is that I need to combine 3 Mail Merge documents into a single outgoing file and one of the Mail Merge documents has multiple records that creates the continuous Section Breaks and additional Paragraph Marks.
0
 

Author Closing Comment

by:Access57
ID: 41884315
Was not able to get any of these recommendations to work, but using their comments I was able to modify my code to find a workable solution.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question