Solved

How can I eliminate Paragraph Marks using VBA?

Posted on 2016-11-11
7
47 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
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 49

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

16 Experts available now in Live!

Get 1:1 Help Now