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.

 

[TonyJollans]

You are making some assumptions about what you have that are confusing, and it's difficult to know what you have and what you want to achieve.

A Section Break is a Section Break is a Section Break, and I don't know what you mean by a 'soft' Section Break. You can delete Section Breaks but the results are not necessarily what you expect. Similarly a Paragraph, no matter how many lines, ends in a Paragraph Break.

Also, I would not expect there to be 'random' characters, or constructs, in any report generated in Access, unless they come from the source data.

In general, exporting from Access and then manipulating the result in Word is, probably, a poor solution. If you format your report as you want it in Access, the actual way in which the document layout is achieved in Word shouldn't be something to be concerned about, as the Document is not being maintained in Word - or, if it is to be maintained in Word, you have a one-off exercise, which might as well be manual.

It's hard to say much more without a bit more detail.

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]

I'm not sure what details you need. There are breaks at points when there's a lot more "white" and room for text that got moved to a new page and the section is not complete. There are end of paragraph marks at lines in the middle of a paragraph and not just at the end so that if I remove them manually the paragraph wraps properly but not until then.

I have formatted the dcoument so that it looks perfect in Access but once exported to Word these problems show up and I really don't have time to rewrite this as it is an urgent deadline. The only solutions I've been given are to insert a ton of bookmarks in a word file and export the data out piece by piece which I don't have time for.

 

[BeckyMc]

I did notice that I had several backwards Ps in between the last line of text and the section break after turning on hidden characters. I thought I found code to remove those but after 15 minutes it still wasn't done with a 3 page document.

Is there a quick way to loop and remove those? I guess they're end of line characters or end of paragraph marks.

 

[SkipVought]

either vbLF or vbCR.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TonyJollans]

Without seeing the actual thing it's very hard to know what is wrong.

The backward P is called a pilcrow - ¶ - and is used as an end of paragraph mark by Word. If you have consecutive empty paragraph marks that may be reflecting something in your Access report, because I wouldn't expect Access to generate them to create white space.

I think, when you refer to paragraph marks you probably mean manual line breaks - again I wouldn't expect Access to generate these but perhaps it's a facet of using RTF.

If you have white space where you think text should fit, is your page size the same in Access and Word?

As for 'sections' I think you are using the term as meaning something to you, related to your data, rather than as a technical Word term, but I could be wrong.

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]

This is basically what I need but it's taking an awful long time for a 3 page document (more than 7 minutes). I think I got this one from herer.

http://word.tips.net/W012_VBA_Examples.html

suggestions for improvement?



Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Text = "^p^p"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
While Selection.Find.Found
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeBackspace
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.Find.Execute
Wend

 

[TonyJollans]

That's rather poor code, but should work as far as I can see. Running for 7 minutes, however, is seriously wrong - I think I would need to see the document to find the problem.

But, to simply remove duplicate paragraph marks, this should be sufficient:

    With ActiveDocument.Content.Find
        .Text = "^13{2,}"
        .Replacement.Text = "^p"
        .MatchWildcards = True
        .Execute Replace:=wdReplaceAll
    End With

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]

Wow, Thanks Tony! That worked, was incredibly fast, cleared any hidden characters out of the excess white space that was there and yet I STILL have a page break that isn't gone even though there's really nothing there now.

When I turn on hidden characters it has a break at that point, about 1/4 into the page, the rest of the paragraph is on the next page when it could easily fit well on the other 2/3 of the page.

I'm completely confused now as to why it's seeing a need for a page break there. I have 3/4 of the document left as white and what I need on there is only about 1/4 of the white space left.

If I delete the page break manually, everything fits but I'll need to fit the page break automatically!

 

[BeckyMc]

Tried this code to get rid of hte page break which I found in several places but the page break doesn't go away. Does that mean it's a soft page break?

Public Sub DeleteManualBreaks()
With ActiveDocument.Content.Find
.ClearFormatting
.Text = "^m"
With .Replacement
.ClearFormatting
.Text = ""
End With
.Execute Replace:=wdReplaceAll
End With
End Sub

 

[TonyJollans]

I guess what's happening is that the removal of empty paragraph marks has pushed the page break up the page. To remove all hard page breaks ...

    With ActiveDocument.Content.Find
        .Text = "^m"
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll
    End With

but perhaps it's just the first you want to remove, in which case ...

    With ActiveDocument.Content.Find
        .Text = "^m"
        .Replacement.Text = ""
        .Execute Replace:=wdReplace[red]One[/red]
    End With

Doing this may bring together two paragraph marks that had been separated by the break, so it might be better to do this before removing the empty paragraphs.

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]

Neither worked. And the word "page break" is inside the line which implies to me that this is a manual page break. I'm going to see what else I can do on the Access side to see if there's anything else I can "tighten" up but I am really not seeing much left to "tighten up" on that side.

It just wants to insert a manual page break way too soon on the page.

 

[TonyJollans]

[blue]^m[/blue] is the code for a manual page break in Find & Replace. If you have one, that code will replace it.



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]

I thought that was it and even tried the code in its own database on its own button with no luck.

I'm wondering if there's another way to reference a page break in a doc without using ^m

like a pagebreaks collection or something.
activedocument.pagebreaks(i).delete


^m doesn't seem to want to grab this thing.

 

[TonyJollans]

Well, you could try ^12 - or Chr(12) in code outside a Find - but that could also be a Section Break.


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]

Based on a trouble shooting tip about page breaks, I inserted a page break manually. Then I did a find and replace manually on ^m and replacing with "". That worked manually and deleted BOTH page breaks for me.

When I try it with vba it didn't work. *sigh* I"m beginning to think I might have to create a new document and copy/paste my document over paragraph by paragraph to get rid of this thing.

Interestingly when I inserted a manual page break it had the words "page break" but it only drew the page break for a couple inches. The page break I"m trying to get rid of is drawn across the whole page. Is that a section break? Would that be a different code than ^m?

 

[TonyJollans]

If it says "Page Break", it should be a Page Break - if the Replace that worked, worked on the existing break, it's a Page Break. So the question is: why does it not work in the first place? At the moment I don't have an answer for that.

How Word draws Page Breaks in Print View depends on the surrounding text. It should be more obvious and consistent in Normal/Draft View.

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]

I finally grabbed a paragraph in this document and used instr to tell me what the unwanted characters are. They appear to be vbCR at the end of every line.

Hoping for a quick way to replace all of them without using find since that always blows up on me but still searching.

The paragraph is selected so selection.something would work ok.

if instr(1,selection,vbcr)>0 then

is how I finally found out what the characters were.

 

[BeckyMc]

I'll answer my own question and say

selection.text = replace(selection.text,vbcr,"")

Now to loop through paragraphs and separate the one liners I really want.

Maybe I can find a range to select.

 

[BeckyMc]

OK that idea is replacing too much. There's a better way to loop through a selected text and replace vbcr without a Find command but I'm not seeing it yet.

Might try to get find more stable but I'm afraid to use it right now.