Solved: Trying to delete text through VBA...

[Michael 514]

Hi guys!

I just love this place. I post a question, and day by day, the answers trickle in!! I wish I could be more of a help, but I suck at this stuff, big time!

Anyways, I am trying to do something interesting in Word, which would help me a lot.

We use protected forms with tons of bookmarks.

On our forms, we also have "instructions" for the user. All these instructions are in blue 6 pt text.

I WISH that there was a way, with one click of a macro, to erase all this extra, superflouous blue text.

The person filling out the form needs it -- those are the instructions! But it takes up a lot of space, and the end client doesn't need to see the same instructions on every sheet!

So, I am thinking of something along the lines of the following:

Telling the PC to delete anything in blue, 7pt text!

or

Surrounding all this text within bookmarks (ie. Comment1, Comment2, CommentX...), and then having a macro delete everything in Comment1 to CommentX, and then delete the actual bookmark names, too. Then they'd be GONE! No trace!

or

I was thinking of inserting some kind of invisible character before each of these instructions, and then an invisible character after. Then have a macro delete everything in between.

I am thinking I am onto something with the bookmarks.

I would imagine this isn't a hard task.

Does anyone have any ideas?

This would be great!

Thanks!

Mike

[TonyJollans]

Hi Mike,

There are all sorts of ways to identify and delete text - I think I'd probably use a Style.

A quick question first, though. What happens to your forms - are they printed (or written to PDF, etc.) or transmitted as Word documents to your Client? Reason for asking is, if they're printed you might be able to simply use hidden text and set up your screen to see it but your print not to print it.

[Michael 514]

Hey Tony,

A style, eh? Very interesting.

You ask a very valid question...!

They are transmitted as Word docs, viewed on screen.

Therefore, we really don't want them to get wind of the instructions!

If we can kill the text totally, that would be the best thing. That way, I could write things like, "IF YOU DON'T ANSWER THIS QUESTION, YOU ARE NOT GETTING PAID!!!!", without worrying about the client seeing such a thing! :)

Thanks for your help,

Mike

[fumei]

If the document is protected, I am assuming these are formfields? In which case, make the text the F1 Help for the field. That way, they can get the help they need, the instructions they require, and none of it is printed.

[Steiner]

Ok, here's a small macro using Tony's idea about the styles. To test it, create a new Style called "Comment" and assign it to each comment in the document that should be deletet. The following macro searches only for whole paragraphs, so be sure to format accordingly.

The macro simply puts the text to be deleted into the debug window. Remove the ' before the delete command to actually delete text.

Option Explicit
Sub DelText()
Dim par As Paragraph

For Each par In ActiveDocument.Paragraphs
If par.Range.Style = "Comment" Then
Debug.Print par.Range.Text
'par.Range.Delete
End If
Next par
End Sub

[Michael 514]

Thanks Steiner!! I am going to try this after lunch!

Fumei - The F1 thing won't work. I did that before, but NO ONE reads those tiny balloons, or text in the status bar. I would love to put this IN THEIR FACE! :)

Kinda like push vs. pull information.

Can't wait to try this idea, Steiner!

Thanks,

Mike

[petedw]

Just incase you wanted to stick with your deleting the text between the bookmarks idea, try this code out

Sub DelText()
Dim myRange As Range
'"front" = BOOKMARK BEFORE YOUR TEXT
'"back" = BOOKMARK AFTER YOUR TEXT
above = ActiveDocument.Bookmarks("front").Range.Start
below = ActiveDocument.Bookmarks("back").Range.End
Set myRange = ActiveDocument.Range(Start:=above, End:=below)
myRange.Select
Selection.TypeBackspace
'DELETES THE BOOKMARKS
ActiveDocument.Bookmarks("front").Delete
ActiveDocument.Bookmarks("back").Delete
End Sub

[MOS MASTER]

Hi, :hi:

Remember that for Steiners example to work properly you have to give the comments the style : "Comment" This means selecting the text and apply the style Comment. (Or whatever name you give it)

Further more if your code needs to execute in a protected section of the document. You have to unprotect the document before in the code and set the protection back at the end of execution.

So you need something like:
Sub WorkInProtectedDoc()
With Application.ActiveDocument
If .ProtectionType <> wdNoProtection Then .Unprotect Password:=""

'Run Steiners code

.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=""
End With
End Sub


Further more if you have a great ammount of text you could win execution speed by using the Find method of the range or selection object! (But I don't think you have to much comments)

HTH, :whistle:

[Michael 514]

Thanks guys!

Mos, good point with unprotecting the doc!

Yes, my current macro that we run on new reports does do that.

The current macro is a little keystroke macro that I recorded that does this:
1 - unlocks the form
2 - Changes the view to 100%
3 - Changes view to Print
4 - updates fields (twice) to make sure everything adds up.
5 - Deletes all the instructions prior to the form (I put a bookmark in there called REPORTSTART) which it locates, and deletes backwards.
6 - Reprotects the form.

All this is assigned to my little "Begin!" button on the form.
Now I will add in this new stuff!

I can't wait to try it!!

Thanks

Mike

[Michael 514]

Hey Petedw,

I just noticed that we can't have the same bookmark in the document.

It seems to delete the first one.

Therefore, correct me if I am wrong, but using that method, and not the styles method, it will only allow me to find one piece of text. Correct?

...just checking...

[MOS MASTER]

Michael Good luck! :yes

Btw yes you can use the bookmark approach and then you'd best arrange a array of bookmark names to loop through and delete the range of those bookmarks.

Could be fast as well. But I think you'd better try some options first.

HTH, :whistle:

[Michael 514]

It WORKS!!!

Steiner / Mos ==> You guys got it!

Worked like a charm!!

Thank you!

I can't wait to use it!

I love this place!

Mike

[MOS MASTER]

We love it too Michael, :rofl:

Glad we could help you. Would you be so kind to mark your thread solved? :whistle:

[fumei]

1. Can't have the same bookmark in document. That is why you should always explicitly name bookmarks.

2. Why not have instructions as a messagebox displayed with the OnEntry macro of the formfields? The user tabs into the formfield, the instructions dispolay as a messagebox. No text in the document to delete.

3. Back to Tony's idea. If ALL the instruction text was a style (named Instructions), then simply loop through and remove anything with that style.

[Michael 514]

Hi again!

So, on this rainy Sunday afternoon, I figured I would start playing with this cool macro.

I have to make one change, and I hope someone can help. Yeah, I know it's probably as easy as pie, but what can I say, I am an amateur!

So far, this is what I've got:

Sub DelText()
Dim par As Paragraph
For Each par In ActiveDocument.Paragraphs
If par.Range.Style = "Comment" Then
Debug.Print par.Range.Text
par.Range.Delete
End If
Next par
End Sub

It is sweet to watch my text disappear.

However, it doesn't pick up all the Comment styles within tables.

Ironically, 99% (actually, 100%) of our forms are in tables! Therefore, how do I instruct it to look for this style in tables, too?

That is my big question!

And Fumei:
1 - I agree. That is exactly why the style works well, No need for the form designed to keep track of 20 uniquely-named bookmarks!

2 - That is a great idea, and will have some use for us as we get more sophisticated. The only reason it's no good here is because we would love them to print these forms and have them on the road with them while they work. They will therefore see all the instructions.

3 - Yup, that's what I am doing!

...I just have to have it loop through my tables, too.

That IS possible, right?

Can't wait to finish this!

Thanks!

Mike

[fumei]

Your code should work for paragraphs in a table. I have essentially the exact same code, and it removes paragraphs of the given style within tables.

The text in the table IS that style yes?

[Michael 514]

Hi Gerry,

Aha... allow me to clarify.

If it is in it's OWN cell, yes, it will erase.

If it shares a cell with any other text, it stays..

Hmmmm....

Isn't that interesting?

:)

[fumei]

Actually...negative, that is not correct.

I have tested with text in cells. I can delete all paragraphs of the given style, keeping the other paragraphs that are not the style alone.

It works perfectly. All paragraphs with the style are deleted, all paragraphs not of the style are left alone. That is within a cell, or anywhere else. The fact the paragraph is in a cell is not relevant at all.

I am quite sure your issue is the use of styles.

1. Do you use Show/Hide to show paragraph marks? If not, I suggest you start using it ON.

2. Make sure the paragraphs in the cells are the right style. Please do not confuse text with paragraph. Paragraphs are very very specific things to Word.

[Michael 514]

I found the issue!

We need a paragraph before and after the 'comment' text.

Therefore, I cannot end with a comment style in a cell, and expect it to disappear.

Indeed, I need a hard-return after the comment field, and then it will work!

So, what I now did was press Enter after the comment, and then, since I was stuck with a blank line, I simply reduced the font to 1pt.

Therefore, Word will treat this as it's own paragraph, yet the user will not see an extra blank space.

Beautiful!

Thanks for the help!!!

Mike

[MOS MASTER]

Hi Mike, :yes

Glad to see your problem is solved. Please don't forget to mark this thread solved.

Btw there is a easier/faster way to do this like:
Option Explicit
Sub DeleteComments()
With ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting

.Style = ActiveDocument.Styles("Comment")
.Execute Replace:=wdReplaceAll, Format:=True
End With
End Sub


HTH, :whistle:

[Michael 514]

Cool! Even easier!

Thank you!!

I will now mark it as solved!

Have a good day everyone!

Mike

[MOS MASTER]

Glad we could help you Mike! :yes

[fumei]

I found the issue!
Sorry, but I think you have missed the point.


We need a paragraph before and after the 'comment' text.
No you absolutely do NOT. This is totally incorrect. You can have a Comment style with or without other paragraphs. I specifically mentioned do not confuse "text" with "paragraph". Paragraphs are very very specific to Word.


Therefore, I cannot end with a comment style in a cell, and expect it to disappear.
This is not accurate. If the paragraphstyle of the text is Comment – it will disappear. If it is not that style…it will not. So, yes you CAN expect to be able to end with a comment - if it properly uses the style.

Indeed, I need a hard-return after the comment field, and then it will work!
A "hard return" is what makes it a paragraph!


So, what I now did was press Enter after the comment, and then, since I was stuck with a blank line, I simply reduced the font to 1pt.

If you are using styles properly you NEVER, EVER, EVER have to have blank lines, or "extra" paragraphs. This is the power of styles, the step you took, while it does "work" is completely unneeded.


Therefore, Word will treat this as it's own paragraph, yet the user will not see an extra blank space.

Sigh, it does not treat it as its own paragraph…it IS a paragraph.

[MOS MASTER]

Hi Gerry, :yes

As ussual I mist the conversation but that's a nice explanation indeed! :whistle:

[Michael 514]

Hi again, guys!
It's the first Sunday of the month again, VBA play time! :)

OKay, I was wondering if someone could please add one small piece of code to this one:

Sub DeleteComments()
With ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting

.Style = ActiveDocument.Styles("Comment")
.Execute Replace:=wdReplaceAll, Format:=True
End With
End Sub


One of my team members noticed that if a form has no Comment styled paragraphs in it, it will crash! (Of course.. the style isn't found!)

So, how does one write in VBA, do this "If exists "Comment" with the doc." And if not, just skip the whole business.

Of course, a workaround I have been using for the past week is to just shove one character of Comment style into my documents, but that is silly!

So, if someone can help, that would be great!!

Thanks!

Mike

[Steiner]

One way could be to loop through all styles and compare whether the one you want is actually there, but I think simply using On Error might be more efficient. And if you don't want to have this mixed up in your code, you could put that into a function:

Function StyleExists(StyleName As String) As Boolean
Dim Temp As String
On Error GoTo err
Temp = ActiveDocument.Styles(StyleName).NameLocal
StyleExists = True
Exit Function
err:
StyleExists = False
End Function


Daniel

[Michael 514]

Thanks Daniel!
It sounds like a plan.
Again, my ignorance shows.

Where exactly do I put this?

My routine to kill all commented text sits in the middle of a 6-stage macro.

Everytime I slip in your code, I get an anexpected end of Sub error.

Here you will see four parts of my macro, being the following:

1 - Delete all text before the ReportStart bookmark
2 - Set the zoom to 100%
3 - Delete all the commented text
4 - Reprotect the document with a password.

*There are other steps before and after. The beginning starts with a Sub BeginReview() and ends with an End Sub.

So, where exactly do I insert your error-trapping code?

Thanks!

Mike

' Look for the ReportStart bookmark, and delete all all the pages before it
Selection.GoTo What:=wdGoToBookmark, Name:="ReportStart"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1

'Set the zoom to 100%
ActiveWindow.ActivePane.View.Zoom.Percentage = 100


' Delete all Commented text
With ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting

.Style = ActiveDocument.Styles("Comment")
.Execute Replace:=wdReplaceAll, Format:=True
End With

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True, Password:="1"
End If

[MOS MASTER]

Hi, :hi:

Like Daniel metioned the fasted way is is to use On error Resume Next in this case.

In your code that would be:
' Look for the ReportStart bookmark, and delete all all the pages before it
Selection.GoTo What:=wdGoToBookmark, Name:="ReportStart"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1

'Set the zoom to 100%
ActiveWindow.ActivePane.View.Zoom.Percentage = 100

On Error Resume Next 'start here
' Delete all Commented text
With ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting

.Style = ActiveDocument.Styles("Comment")
.Execute Replace:=wdReplaceAll, Format:=True
End With
If Err <> 0 Then Err.Clear 'clear error if needed

' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True, Password:="1"
End If


HTH, :whistle:

[Michael 514]

This is great!

It works!

I also just took those two lines:

On Error Resume Next
and
If Err <> 0 Then Err.Clear

...and I used it for the other part of my macro, where it searches for the "ReportStart" bookmark... Now, when the ReportStart Bookmark is gone, it no longer crashes!

Very cool!

Thanks!

Mike

[MOS MASTER]

Glad we could help you Mike! :beerchug:

[Steiner]

Good to hear it works for you. My final idea, however, was slightly different. I would not use the On error right inside the existing code as there might be other (unexpected) errors that could occur, but put the function in a module and simply call it to check whether a style exists or not (seems a bit tidier to me, but that's just my personal opinion):

Option Explicit

Sub DeleteComments()
If StyleExists("Comment") Then
With ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting

.Style = ActiveDocument.Styles("Comment")
.Execute Replace:=wdReplaceAll, Format:=True
End With
End If
End Sub



Function StyleExists(StyleName As String) As Boolean
Dim Temp As String
On Error GoTo err
Temp = ActiveDocument.Styles(StyleName).NameLocal
StyleExists = True
Exit Function
err:
StyleExists = False
End Function



Daniel

[fumei]

Daniel is absolutely right in his approach. It is much better to use the function. From a design point of view, while there is a use for On Error Resume Next in some cases, it does of course resume after ALL errors. Since we are programming VBA.......it is better to use a function for the specific task of checking for the style, since THAT is what is needed.

[MOS MASTER]

Daniel is absolutely right in his approach. It is much better to use the function. From a design point of view, while there is a use for On Error Resume Next in some cases, it does of course resume after ALL errors. Since we are programming VBA.......it is better to use a function for the specific task of checking for the style, since THAT is what is needed.

Indeed daniel's approach is much cleaner and you will never here me say otherwise! (Nice code D) :rofl:

But: The on Error Resume Next is the fasted method in this case and the function is the clean method. (We've had a nice discussion in the past where I was on the other side of the fence) :*)

Other then that it's very easy to add a general error handler to cope with the remaining unexpected errors in the sub. :whistle:

[Michael 514]

Very interesting.

Yes, as a former amateur programmer (COBOL/74 & Pascal!), I can understand where the original On Error was a "quick" way out! :)

This is great stuff.

It's working well now!

Thank you again,

Mike

[MOS MASTER]

Very interesting.

Yes, as a former amateur programmer (COBOL/74 & Pascal!), I can understand where the original On Error was a "quick" way out! :)


Indeed depending on the execution speed of your code you can use tools like that to error over steps. (But you always have to know what you're doing...writing a function like Daniel...it shows that he knows what is happening)

Good luck! :whistle:

[Steiner]

:blush thanks Joost - I am trying to pretend that.

Daniel

[MOS MASTER]

:blush thanks Joost - I am trying to pretend that.

Daniel

No you are not my friendly neighbour! :nono

Later buddy. :yes