Word VBA deleting Soft Section Breaks and Soft End of Line Chars 1

[BeckyMc]

I'm in a huge rush and down to the line on this project. I'm great with Access VBA but with Word VBA I'm pretty weak. I have a document that I've created by exporting from Access to RTF, then openened a file w custom header/footer in word, then copied rtf file to tformatted file.

Contents are basically OK except that I get random soft section breaks that I can NOT delete except for manually and I get end of line returns mid paragraph that I need to delete.

I've tried many different ideas but nothing really works. Please help!!!

I keep finding example code to insert a page break and I want to delete a section break. Can I do that if it's a soft break?

Do I loop pages or sections?

What if I have a one line "paragraph" for lack of a better word and want to keep that end of line character, just not the end of line characters in the middle of certain paragraphs?

This is truly an urgent need for a small group of people that have really been "beaten up" by "experts" that didn't show any progress on this project for NINE months!!! I turned that around but can't get this!!

And of course the body of this document is constantly dynamic.

 

[BeckyMc]

I think this is answering my question again

http://word.tips.net/Pages/T000583_Processing_Information_Pasted_from_a_PDF_File.html

 

[macropod]

Hi Becky,

Here's a macro that will clean up text pasted from websites, PDFs and emails:

Sub CleanUpPastedText()
With ActiveDocument.Content.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchAllWordForms = False
  .MatchSoundsLike = False
  .MatchWildcards = True
  .Text = "([!^13])([^13])([!^13])"
  .Replacement.Text = "\1 \3"
  .Execute Replace:=wdReplaceAll
  .Text = "([ ])[ ]{1,}"
  .Replacement.Text = "\1"
  .Execute Replace:=wdReplaceAll
  .Text = "[^13]{2,}"
  .Replacement.Text = "^p"
  .Execute Replace:=wdReplaceAll
  .Text = "[^13])"
  .Execute Replace:=wdReplaceAll
End With
End Sub

Provided your document has two or more paragraph breaks separating the logical paragraphs, it should clean up your document too - except for those peculiar page breaks you keep talking about.

Cheers
Paul Edstein
[MS MVP - Word]

 

[TonyJollans]

vbCr is one way to refer to a paragraph mark in VBA - it's the same character represented by ^p or ^13 in Find & Replace.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[BeckyMc]

Find is still crashing on me. This is really the sort of thing that I want with attaching a bookmark whenever there is a gap because that's the only consisistency I have. I don't have two end of par marks or two vbcr. I have one vbcr and 1 blank line. So if I can book mark each "section" in between the blank lines I can then loop through the bookmarks and clean stuff up very nicely.

After the string that starts with "Respond" there's other address stuff that should be on one line each.

right now it doesn't appear to be moving down the document. The first paragraph again is cleaned up nicely. Find is still crashing on me. I just can't seem to move through the document.

There's probably too many variables declared now too as I"ve been trying so much.

Private Sub Command71_Click()

Dim n As Integer ' loop variable
Dim r As Range ' range object variable


Dim MyUserName As String

Dim WordObj As Object
Dim ObjWordDoc As Object
Dim ObjWordDoc2 As Object
Dim oPage As Object

Dim Rng As Object

Dim MyFileName As String

Dim OShp As Shape
Dim oRg As Range

Dim MyPar As Paragraph
Dim MyCleanedupPar As Paragraph
Dim MyChar As Variant
Dim x As Integer
Dim y As Integer
Dim MyPos1 As Integer
Dim MyPos2 As Integer
Dim MyParBMNo As Integer
Dim MyBM As String

Dim MyStartStr As String
Dim MyEndStr As String



Dim NumPages As Integer


Dim MyFacilityName As String

MyUserName = ap_GetUserName

DoCmd.OutputTo acOutputReport, "R_NOV", acFormatRTF, "C:\Documents and Settings\" & MyUserName & "\My Documents\Part1.DOC"

MyFacilityName = Forms!F_Inspections_Main!cboFindFacilityName.Column(1)

'On Error Resume Next

'Set WordObj = GetObject(, "Word.Application")

'If Err <> 0 Then
Set WordObj = CreateObject("Word.Application")
'End If

WordObj.Visible = True

WordObj.Documents.Open ("C:\Documents and Settings\" & MyUserName & "\My Documents\Part1.DOC")

WordObj.Documents("Part1.doc").Activate

ActiveDocument.Select
Selection.WholeStory
Selection.Copy

WordObj.Documents.Open ("C:\Documents and Settings\" & MyUserName & "\my documents\NoticeTemp.doc")

Selection.PasteAndFormat (wdPasteDefault)

Windows("NoticeTemp.doc").Activate

ActiveDocument.SaveAs ("C:\Documents and Settings\" & MyUserName & "\My Documents\NEWNOTICE.doc")

ActiveDocument.Bookmarks.Item("HeaderBookmark").Range.Text = MyFacilityName

ActiveDocument.GoTo.Text = "dear"

ActiveDocument.GoTo(What:=wdGoToLine, Which:=wdGoToNext, Count:=13).Select

Selection.MoveDown unit:=wdLine, Count:=5, Extend:=wdExtend

'Clean up "Pargraph" 1 each line has a vbcr on the end.

x = InStr(Selection.Text, vbCr)
y = 1

While x > 0
Selection.Text = Left(Selection.Text, x - 1) & Mid(Selection.Text, x + 1)
y = x + 1
x = InStr(y, Selection.Text, vbCr)
Wend

'append a real vbcr on the end of the paragraph which also works well
Selection.MoveEnd
Selection.Text = Selection.Text & vbCr

'try to move to the next line and loop through the existing "paragraphs" for lack of a better word
'This gives me an infinite loop and looks like it starts back at the first paragraph.

Selection.GoTo What:=wdGoToLine, Which:=wdGoToNext, Count:=1, Name:=""

If ActiveDocument.Bookmarks("\line").Empty Then
Selection.MoveDown unit:=wdLine, Count:=1
End If

MyParBMNo = 1

While Not MyEndStr = "respond"

If Selection.Text = "" Then
Selection.GoTo What:=wdGoToLine, Which:=wdGoToNext, Count:=1, Name:=""
Else
MyEndStr = Left(Selection.Text, 7)
MsgBox "My End String " & MyEndStr
MyStartStr = Mid(Selection.Text, 2, 9)
MsgBox "My Start String " & MyStartStr
If MyStartStr = "failure" Then
While Not ActiveDocument.Bookmarks("\line").Empty
Selection.MoveDown unit:=wdLine, Count:=1, Extend:=wdExtend
Wend
ActiveDocument.Bookmarks.Add Name:=MyBM & MyParBMNo, Range:=Selection.Range
MyParBMNo = MyParBMNo + 1
End If
End If

Wend



WordObj.Visible = True


WordObj.Quit

Set WordObj = Nothing

MsgBox "File Created"

Set WordObj = Nothing
End Sub

 

[PHV]

You should use Full Qualified Names!
e.g.:
WordObj.ActiveDocument.Select
WordObj.Selection.WholeStory
WordObj.Selection.Copy
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BeckyMc]

Great idea and thanks! That might be the cause of the find crashing. Just wondering if I can do a better loop than what I have since I don't have ^P^P to search on.

Only vbcr and then a blank line

 

[TonyJollans]

You don't really want to be using Selection if possible, but this seems to be getting more and more complex all the time. Could you post the RTF file (or the database) somewhere, or send me a copy confidentially?


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[BeckyMc]

Yes it is turning into a monster. Not sure how to contact people privately but you're welcome to contact me privately if you have that ability.

Afraid to post this even though it is a public record database

 

[BeckyMc]

This is an attempt to post the contents of the document that I'm trying to clean up. It does not contain the header and footer.

March 10, 2011
CERTIFIED MAIL
RETURN RECEIPT REQUESTED
Bill Jones, Owner cc: Joe Sample, Property Owner(s)
Bill’s Store unknown
130 Smith St Raleigh, NC 11111
Raleigh, North Carolina 11111
SUBJECT: Notice of Violation
Bill’s Store (510006C)
Raleigh, Wake County, North Carolina
DSCA MMP -- Violation of 15A NCAC 02S.0202(b)
NESHAP -- Violation of 40 CFR, Part 63, Subpart M
Dear Mr. Smith:
On March 02, 2011, John Smith Compliance Inspector(s) with … conducted a compliance inspection at Mike's Cleaners. During this inspection, violations of the
following state and federal environmental regulations were documented:
DSCA Minimum Management Practice (MMP) rules 15A NCAC 02S.0202(b)
1 . Failure to utilize spill containment under and around the waste solvent storage area by
January 1, 2002 [15A NCAC 02S.0202(b)(2)]. Spill containment shall have a
volumetric capacity of 110 percent of the largest vessel, tank, or container within the
spill containment area and shall be capable of preventing the release of the applicable
dry cleaning solvent beyond the spill containment area for a period of at least 72
2 . Failure to install spill containment that is capable of preventing the release of the
applicable liquid dry cleaning solvent beyond the spill containment area for a period of
at least 72 hours [15A NCAC 02S.0202(b)(2)].
3 . Failure to install spill containment with a volumetric capacity of 110 percent of the
largest vessel, tank, or container within the spill containment area and capable of
preventing the release of the applicable dry cleaning solvent beyond the spill
containment for a period of at least 72 hours. [15A NCAC 02S.0202(b)(2)].
4 . Failure to maintain emergency adsorbent spill cleanup materials on site. [15A NCAC
02S.0202(b)(2)].
National Emission Standards for Hazardous Air Pollutants 40 CFR, Part 63, Subpart M
5 . Failure to store wastes that contain PCE in closed containers with no perceptible leaks.
Separator water containers shall be sealed when the machine and still are not in
operation [63.322(j)].

6 . Failure to maintain a complete five-year history of perc purchase receipts on site
[63.324(d)]
7 . Failure to record volume of perc purchased each month [63.324(d)(1)]. Logs must be
maintained on site for a period of five years and available for review.
8 . Failure to monitor on a weekly basis either the refrigeration system high pressure and
low pressure readings during the drying phase to determine if they are in the range
specified in the manufacturer’s operating instructions, or the condenser exit
temperatures to ensure the perc vapor air stream flowing through the refrigerated
condenser before the end of the drying cycle does not exceed 45 degrees Fahrenheit
(7.2 degrees Celsius) [63.323(a)(1)(i) or (ii)].
9 . Failure to keep repair log and maintain this information on site for five years
[63.324(d)(4)].
10 . Failure to record dates and results of weekly inspections of the dry cleaning system
components for five years [63.324(d)(3)]. Logs must be maintained on site for a period
of five years and available for review.
11 . Failure to record dates and results of the monthly halogen detector vapor leak
inspections while the components are in operation [63.324(d)(3)].
12 . Failure to record weekly the date and the results of the refrigerated system high/low
pressure readings or the refrigerated condenser exit temperatures [63.324(d)(5)]. Logs
must be maintained on site for a period of five years and available for review.
13 . Failure to conduct weekly perceptible leak inspections while machine is in operation
[63.322(k)].
14 . Failure to calculate and record the result of the yearly perc consumption determined on
the first day of each month for five years [63.324(d)(2)]. Logs must be maintained on
site for a period of five years and available for review.
The above listed violations and any future violations of any of the applicable DSCA MMP and
NESHAP regulations are subject to the assessment of civil penalties of up to twenty-five thousand
dollars ($25,000) per day in accordance with North Carolina General Statute 143-215.114A.
Respond in writing to the address below by describing all actions taken to bring about
compliance.

 

[macropod]

Hi Becky,

Re:

I have one vbcr and 1 blank line.

Unless the blank line is created by applying a before or after space setting to the last line in a logical paragraph and/or the first line in the next logical paragraph, it has almost certainly been created via a paragraph break. The only other alternative is a manual line break as the first or last character in a paragraph.

As for:

So if I can book mark each "section" in between the blank lines I can then loop through the bookmarks and clean stuff up very nicely.

If you change the 'ActiveDocument.Content' in the macro I posted to 'Selection', you can select a block of text and have the macro run on that selection alone.

However, the more fundamental issue seems to be that whatever process you're using to create these reports is not working correctly. You should not be getting breaks at the end of each line or page or Section breaks where they shouldn't be. IMHO, that's where the problem should be addressed, not as a fix after the event.

Cheers
Paul Edstein
[MS MVP - Word]

 

[BeckyMc]

I don't have a choice about process. Access gives the option to export to rtf or to export piece by piece to bookmarks and I don't have time for that with this deadline.

But I'm now getting mixed messages about using selection versus not using selection.

I'm hoping I've cleaned up enough to use the find to search on a text and do bookmarks between the beginning and end point.

 

[macropod]

Hi Becky,

As Tony said:

You don't really want to be using Selection if possible

That advice is entirely consistent with mine about using Selection in the CleanUpPastedText macro where you want to limit the code's scope to a selected range.

Cheers
Paul Edstein
[MS MVP - Word]

 

[BeckyMc]

Why can I not get a book mark when debug.print shows me text? And I love the Find answers but it still crashes on me even though I thought we solved it so I can NOT use Find.


Foundstr is dimmed as integer
MyPar is dimmed as paragraph

for each mypar in activedocument.paragraphs

foundstr = instr(1,mypar.range.text,"respond")
debug.print mypar.range.text

if foundstr>0 then
mypar.range.select
activedocument.bookmarks.add range:=selection.range, name:="endofbody"
goto MyExit:
end if

next mypar

When I use debug.print on mypar.range.text I definitely see the text including the line I want to search via instr. But I never get the bookmark I want to add to the paragraph I find. I've tried various forms of syntax.

The word "Respond" is at the beginning of the paragraph that I want but I thought something greater than 0 would always be returned if your string was found.

 

[BeckyMc]

This is working instead. I have bookmarks on every paragraph now. So hopefully I'll be able to loop and search them. It's late so it's assuming I have no typos in this!

numpars = activedocument.paragraphs.count
n = 1
for n = 1 to numpars

smypar = activedocument.paragraphs.range.text
activedocument.paragraphs.range.select
mynewbookmarkname = "Bookmark" & n
activedocument.bookmarks.add name:=mynewbookmarkname, range:=selection.range

next n