Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I eliminate Paragraph Marks using VBA?

Posted on 2016-11-11
7
Medium Priority
?
96 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 40

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 85

Assisted Solution

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

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

Accepted Solution

by:
Rgonzo1971 earned 750 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 40

Assisted Solution

by:PatHartman
PatHartman earned 375 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

783 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