Solved

How can I eliminate Paragraph Marks using VBA?

Posted on 2016-11-11
7
33 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
7 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
How about this:

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

Accepted Solution

by:
Rgonzo1971 earned 250 total points
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Access57
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now