Tidy up

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Francis Hookham - 24 Jun 2007 14:46 GMT

Checking text before inclusion in my club bulletin, I use a 'Tidy up' macro
to get rid of white space, make sure there is a space after a period and
comma and not before, etc, etc. With MVP help it also makes sure there is
not a space after a full stop and comma in a figure, as in £1,000.00

The only remaining problem is to make sure there are not spaces in URLs.

Any suggestions?

Francis Hookham

Reply to this Message

Russ - 24 Jun 2007 19:09 GMT

Searching for :// might get you there to visually check the url. But how is
code going to know that a trailing space is or is not there to terminate the
url address?

> Checking text before inclusion in my club bulletin, I use a 'Tidy up' macro
> to get rid of white space, make sure there is a space after a period and
[quoted text clipped - 6 lines]
>
> Francis Hookham

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Francis Hookham - 24 Jun 2007 20:24 GMT

Thanks for the thought Russ - having found the URL at the beginning of the
'Tidy up' macro the 'dots' could be changed for something unlikely such as
'zxz' and then, after all the corrections in the body of the macro have been
made, change the 'zxz' bac to 'dots'. But how, having found the URL, can it
be isolated in order to do a Replace and then go on to find any others?

I think I could do it in XL but not in Word. Can you or anyone?

Francis

> Searching for :// might get you there to visually check the url. But how
> is
[quoted text clipped - 13 lines]
>>
>> Francis Hookham

Reply to this Message

Russ - 24 Jun 2007 23:59 GMT

Inline reply

> Thanks for the thought Russ - having found the URL at the beginning of the
> 'Tidy up' macro

Are you implying that the tidy up macro is already capabable of finding
urls? How?
Unless they are delimited by special characters or formatting, they will be
hard to find.
Otherwise, as I said in my last post, how will you know where the url ends?
At which space character?

Your macro might make:
**Http://google .com This sentence is not part of url.**
Look like
**Http://google.comThissentenceisnotpartofurl.**

>the 'dots' could be changed for something unlikely such as
> 'zxz' and then, after all the corrections in the body of the macro have been
> made, change the 'zxz' bac to 'dots'.

Why are you concerned about the dots?

> But how, having found the URL, can it
> be isolated in order to do a Replace and then go on to find any others?

Replacement is the easier part.

> I think I could do it in XL but not in Word. Can you or anyone?
>
[quoted text clipped - 17 lines]
>>>
>>> Francis Hookham

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Francis Hookham - 25 Jun 2007 21:48 GMT

Russ, sorry - I'm not making myself clear as usual.

The 'Tidy up' macro, intended to cope with typos, carries out a sequence of
Replaces in order to eliminate multiple spaces and ensure there is a space
AFTER a period (UK calls them full stops) and not BEFORE. Similarly there is
control over spaces associated with commas and various other things like
parentheses and exclamations.

One problem is that numbers like 1,000.00 end up as 1, 000. 00 but that is
corrected at the end of the macro by the code copied below.

What I should like to achieve, at the beginning of the macro, is to find
'www.' at beginning of a URL, then replace the 'dots' with something
unlikely to appear in the text as a whole (maybe 'xox').

The carry out the 'Tidy up'.

The, at the end of the macro, replace the 'xox's with dots.

Searching for 'www.' would bring us to:

http://www.cambridgerotary.org.uk/kluby.php?id_klubu=3

How can we select everything backwards to the first space (picking up
http:// if it is included) and forward to the first space in order to do a
Replace of the dots with a temporary 'xox'?

It is beyond me?

Francis

'No space in #,##0.00

With Selection.Find

.ClearFormatting

.Replacement.ClearFormatting

.Text = "(.)([!0-9])"

.MatchWildcards = True

.Replacement.Text = "\1 \2"

.Execute Replace:=wdReplaceAll

End With

With Selection.Find

.ClearFormatting

.Replacement.ClearFormatting

.Text = "(,)([!0-9])"

.MatchWildcards = True

.Replacement.Text = "\1 \2"

.Execute Replace:=wdReplaceAll

End With

> Inline reply
>
[quoted text clipped - 51 lines]
>>>>
>>>> Francis Hookham

Reply to this Message

Russ - 26 Jun 2007 06:53 GMT

Francis,

If Word has changed all your urls to hyperlinks you could change their font
to hidden. As a matter of fact, you could change the whole document font to
hidden (Activedocument.Range.Font.Hidden = True) and then expose the text
you want to act on by changing that to unhidden. Or Vice Versa, hide what
you don't want to interact with, if that way is faster. Some people do the
same thing by highlighting text temporarily. And search through the
unhighlighted text.

Anyway you can hide all the hyperlinks.
Dim aLink As Word.Hyperlink
For Each aLink In ActiveDocument.Hyperlinks
aLink.Range.Font.hidden = True
Next aLink

You could also narrow it down to just urls that are hyperlinks:
Dim aLink As Word.Hyperlink
For Each aLink In ActiveDocument.Hyperlinks
If InStr(aLink.Range.Text, "://") > 0 Then
aLink.Range.Font.hidden = True
End If
Next aLink

Then search for stops:
Dim aRange As Word.Range
Set aRange = ActiveDocument.Range
With aRange.Find
.Text = "."
.Font.hidden = False
.Replacement.Text = ". "
.Execute Replace:=wdReplaceAll
End With

And then when finished unhide those things hidden:
Dim aLink As Word.Hyperlink
For Each aLink In ActiveDocument.Hyperlinks
If InStr(aLink.Range.Text, "://") > 0 Then
aLink.Range.Font.hidden = False
End If
Next aLink

Or:
Activedocument.Range.Font.Hidden = False

> Russ, sorry - I'm not making myself clear as usual.
>
[quoted text clipped - 116 lines]
>>>>>
>>>>> Francis Hookham

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Francis Hookham - 26 Jun 2007 16:28 GMT

Many thanks Russ - between Graham and you I have the answer - I am most
grateful

Francis

> Francis,
>
[quoted text clipped - 171 lines]
>>>>>>
>>>>>> Francis Hookham

Reply to this Message

Francis Hookham - 28 Jun 2007 15:32 GMT

All seems to be working well - I hid hyperlinks at the beginning of TidyUp
and unhid them at the end (code below).

Thanks again both Russ and Graham

Francis

At the beginning:
With ActiveDocument.Styles("Hyperlink").Font
.Hidden = True
End With
ActiveDocument.Styles("Hyperlink").BaseStyle = "Default Paragraph Font"

and this at the end:
With ActiveDocument.Styles("Hyperlink").Font
.Hidden = False
End With
ActiveDocument.Styles("Hyperlink").BaseStyle = "Default Paragraph Font"

> Francis,
>
[quoted text clipped - 171 lines]
>>>>>>
>>>>>> Francis Hookham

Reply to this Message

Russ - 28 Jun 2007 18:06 GMT

That's great Francis,
Just an observation. You don't need a With...End With when there is only one
property or method inside the statements. With...End With is for those times
when there are more than one property or method inside the statements.
Use:
ActiveDocument.Styles("Hyperlink").Font.Hidden = True
and
ActiveDocument.Styles("Hyperlink").Font.Hidden = False

> All seems to be working well - I hid hyperlinks at the beginning of TidyUp
> and unhid them at the end (code below).
[quoted text clipped - 190 lines]
>>>>>>>
>>>>>>> Francis Hookham

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Russ - 28 Jun 2007 18:17 GMT

Francis,
My last post observation must of been tunnel vision.
You could have put more than one property or method inside the With...End
With statements.
Use:
With ActiveDocument.Styles("Hyperlink")
.Font.hidden = True
.BaseStyle = "Default Paragraph Font"
End With
And
With ActiveDocument.Styles("Hyperlink")
.Font.hidden = False
.BaseStyle = "Default Paragraph Font"
End With

Although I'm not sure if you need the .BaseStyle set in either or both
places.

> All seems to be working well - I hid hyperlinks at the beginning of TidyUp
> and unhid them at the end (code below).
[quoted text clipped - 190 lines]
>>>>>>>
>>>>>>> Francis Hookham

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Russ - 28 Jun 2007 19:10 GMT

Francis,
Me again. I know that you may be new to programming so I have another 'in
line' comment below.

> Francis,
> My last post observation must of been tunnel vision.
[quoted text clipped - 13 lines]
> Although I'm not sure if you need the .BaseStyle set in either or both
> places.

When not sure whether parts of code are needed, a programmer will
temporarily 'comment out' those parts and rerun the code to see what
difference it made not to run those parts.
In VBA, you make a comment with ', the apostrophe.
So in this case, you would temporarily put an apostrophe before each
.BaseStyle line and rerun the code to see if the lines were needed.
If not needed, you could delete the lines.

The VBA Editor has an Edit Toolbar to help comment and uncomment and indent
and un-indent selected parts of code; and create flags to help navigate
code. You may need to go the View/Toolbars/Edit Menu in the VBA Editor to
make that toolbar visible.

>> All seems to be working well - I hid hyperlinks at the beginning of TidyUp
>> and unhid them at the end (code below).
[quoted text clipped - 190 lines]
>>>>>>>>
>>>>>>>> Francis Hookham

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Francis Hookham - 05 Jul 2007 11:42 GMT

Hi Russ

I've been otherwise engaged for a few days so, apologies for no
acknowledgement until now.

Many thanks for your helpful suggestions - indeed I have all the useful
buttons on my own custom VBA toolbar and I am all the time 'commenting in
and out' lines of code to see what I can cut out. Now, at last, I can make
custom toolbars which run in individual Word and XL files there are all
sorts of things I can do which can be used by others - it's great to have
this amazing community helping each other.

Best wishes

Francis

> Francis,
> Me again. I know that you may be new to programming so I have another 'in
[quoted text clipped - 247 lines]
>>>>>>>>>
>>>>>>>>> Francis Hookham

Reply to this Message

Graham Mayor - 26 Jun 2007 08:05 GMT

Use the fact that Word will format Hyperlinks with a Hyperlink style
At the start of your 'tidy up macro' ensure that hyperlinks are formatted as
such by using the line

Options.AutoFormatReplaceHyperlinks = True
(you cannot use this once spaces have been added)

You can then later remove any spaces you have later added from the hyperlink

With Selection
.Range.AutoFormat
.HomeKey Unit:=wdStory
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "(.)[ ]{1,}"
.Replacement.Text = "\1"
.Style = "Hyperlink"
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute replace:=wdReplaceAll
End With
End With

As for the numbers
replace ([0-9].)[ ]{1,}([0-9])
with \1\2

http://www.gmayor.com/replace_using_wildcards.htm

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Russ, sorry - I'm not making myself clear as usual.
>
[quoted text clipped - 127 lines]
>>
>> drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message