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
Solved

How can I eliminate Paragraph Marks using VBA?

Posted on 2016-11-11
7
57 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 35

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 50

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 35

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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