Macro to add text at start and end of each line 2

[sinbad99]

Be grateful for guidance for a macro for adding some text at the beginning and end of many lines of text in Word.

 

[sinbad99]

This is the macro I have been using, but it does seem a bit clunky. And is it possible to add some text at the end of the line at the same time?

Sub AddHello()
Dim oRg As Range
Set oRg = Selection.Range
With oRg.Find
.MatchWildcards = True
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([!^13]{1,}^13)"
'.Replacement.Text = "hello\1"
.Replacement.Text = "<a href=""ancient_classics/\1"
.Wrap = wdFindStop
.Execute Replace:=wdReplaceAll
End With
Set oRg = Nothing
End Sub

 

[fumei]

Well if you select the text...

Sub Start_End()
Selection.Text = "Add to start " & Selection.Text & _
" Add to end"
End Sub

So if you selected "yadda yadda yadda" - and it can be ANY length - you end up with:

Add to start yadda yadda yadda Add to end

 

[fumei]

Just as a note, the string at the start (or end) does NOT have to be actual text you have in the macro. It can be ANYTHING that is a string value. It could text from another location in the document, text from another open document, text from a cell in a table, text from a bookmark, text from a document variable, text from an inputbox getting user input...and so on.

 

[sinbad99]

Thanks very much, Fumei. So I take it that whatever text is selected/highlighted will be wrapped in whatever I enter into those fields.

I've tried it and it certainly does that. Many thanks. And the coding is so simple!

However, sadly, I want to perform this on dozens of separate lines - as in a list of web addresses that I want to wrap in html code.

Is there a way to adjust the macro so that it does that, please? I suppose it won't be 'Selection.Text' but some sort of 'EachLine.Text' fashion?
Cheers paul

 

[macropod]

If you want to add tags both before & after the strings, you could use something like:

Sub AddTags()
With Selection.Find
  .MatchWildcards = True
  .ClearFormatting
  .Replacement.ClearFormatting
  .Text = "([!^13]{1,})"
  .Replacement.Text = "<a href=""ancient_classics/\1</a>"
  .Wrap = wdFindStop
  .Execute Replace:=wdReplaceAll
End With
End Sub

The above adds your <a href=""ancient_classics/' prefix and a '</a>' suffix to every line.

Cheers
Paul Edstein
[MS MVP - Word]

 

[sinbad99]

Excellent, Paul, and thanks very much for that. I notice that the cursor has to be at the start of the list, or the macro seems to use just the last line. I'm looking through each line of the macro, but I don't see where it says that. (It's probably mentioned in WordTips' big compilation of macro advice at my elbow here.)

I need this for my free ebook website, eBookTrove.com, as I have huge lists of books to be coded. I use Dreamweaver CS5, but strangely enough neither Adobe nor Macromedia before them thought to make it possible to drag large numbers of links onto the workspace in one go.

It seems then that the best way is to copy the list into Word, and use a macro to add the coding around the links. This 'ancient_classics' category that I'm coding and uploading at the moment has more than 700 titles. That's more than 700 separate actions to get the links onto the workspace.

As I've bought thousands of ebooks for the site, that process seems to take forever.

Thanks enormously for this shortcut of yours, Paul. I'll salute your help (mentioning tek-tips), and fumei's, on the ancient-classics category page - probably tomorrow.
Cheers paul

 

[macropod]

When 'Selection' is combined with '.Wrap = wdFindStop' the selection designates the starting point (and end point if even one character is selected). If you want to process the whole body of the document, irrespective of what's selected, change 'Selection' to 'ActiveDocument.Range'.

Cheers
Paul Edstein
[MS MVP - Word]

 

[sinbad99]

Just like magic. Thanks, Paul. I've put a thanks to you on eBookTrove's ancient_classics page, and a link to this thread, too. Many thanks.

 

[sinbad99]

Is it possible to take the macro idea a stage forward - or is this sheer wild optimism?

When I use the macropod macro on 700 links, I achieve this ... and that's a very big step ahead.
..................
<a href="ancient_classics/the_wasps_by_aristophanes.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time."> </a><br />
<a href="ancient_classics/the_water_carriers_oscar_fay_adams.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time."> </a><br />
<a href="ancient_classics/the_way_of_an_eagle_ethel_mdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time."> </a><br />
etc etc etc (699 times)
................

Is it possible to state, to copy the words following '_classics/' and add them to the space after '.">?
And to replace the underscores with a space?
And then what about setting the words in title case?
Then removing '.epub'?

So the ideal result from running this second macro after the first would be ...

<a href="ancient_classics/the_way_of_an_eagle_ethel_mdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.">The Way of an Eagle Ethel Mdell </a><br />

Of course that result wouldn't be perfect, but it would be an astonishing timesaver.

 

[macropod]

Yes, all of that is possible, though getting a true title case (ie without capitalising every word) is more of a challenge.

Before proceeding, though, how much text is originally present after '.epub' (including quotes/punctuation)? I can't tell from your post whether each line already has:
" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.
If that's being added by your macro, it makes a significant difference to how I'd code things. Also, does a period (ie '.') ever occur in a title?

Cheers
Paul Edstein
[MS MVP - Word]

 

[sinbad99]

Very good of you to be thinking about this now. Thanks very much, Paul.

Yes, the words in the title tags are mostly there for Search Engine Optimising, and are the same. Of course, in a macro I can change them around from time to time.

So in that context, '.epub' is at the end of the address for the hyperlink. That has to end in quotation marks. Then there's a space, and then the title tag, 'title="' and its message which is contained within quotes. That is to say that it always must end in quotation marks. I always put a full point before the final quotes. Then, to round it off, the square brackets close.

The affix 'epub' is, of course, just like Word's '.doc' Sometimes it's '.dotx', and similarly the affix in ebook formats changes, too. But I can change that in the macro to suit the list I'm working on.

The actual title of the ebook doesn't have to be in title case. But the first letter ought to be in caps, of course. Conversely, of course, it won't do much harm if the first letter of all the words in the book title are capped - I mean, 'a', 'of' etc.

And the whole thing, the site, is strictly a non-money making thing. It's a hobby that shares ebooks I have bought and so hopefuly the visitors are not going to be sticklers for absolutely correct punctuation. (And many of them are Americans, so they wouldn't recognise proper punctuation. )

Very good of you to think about this. As well as helping enormously with the coding for the site, it is bringing back a lot of what I used to know of Word macros. So it's enjoyable for me in this other way, too.

 

[macropod]

hi sinbad99,

What does the text look like before you've done any processing? For example:
"ancient_classics/the_wasps_by_aristophanes.epub"
or:
"ancient_classics/the_wasps_by_aristophanes.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time."

With the output, does it matter if all the underscores are removed and the title is converted to proper case on both sides? For example:
<a href="ancient_classics/The Way of an Eagle Ethel Mdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.">The Way of an Eagle Ethel Mdell </a><br />

I also note that you mentioned removing '.epub', but your 'ideal' result still includes it. Please confirm what the correct output is.

Cheers
Paul Edstein
[MS MVP - Word]

 

[sinbad99]

Hello Paul
1. Before there's any processing, there's just the name of the file. It usually has spaces between each word, and the case is erratic.

I put the original epub file into its place - folder - on the site, in this case 'ancient_classics'. If I coded with VBA, that would be 'AncientClassics'. (Of course, it could be either, but these days I use the php/mysql way with an underscore between each word.)

So the next stage is to make it read 'ancient_classics/the_wasps_by_aristophanes.epub'

2. You ask if this would be ok - '<a href="ancient_classics/The Way of an Eagle Ethel Mdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.">The Way of an Eagle Ethel Mdell </a><br />'

This part, the code, mustn't have spaces -'<a href="ancient_classics/The Way of an Eagle Ethel Mdell.epub"

So '/The Way of an Eagle Ethel Mdell.epub' should be '/TheWayOfAnEagleEthelMdell.epub' or with underscores in the spaces.

3. Sorry, I've not been clear.Ideally, the part that appears as text on the page, that is the words that follow '.">'wouldn't have the format showing, that is the '.ePub' affix.

So here is the ideal way that an ebook would go onto the server ...

<a href="ancient_classics/the_way_of_an_eagle_ethel_mdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.">The Way of an Eagle by Ethel Mdell </a><br />

I know I've added a 'by' because it seems to be the name of the author, but that isn't necessary, and if it were, could be added by hand.

Phew, I've troubled you with lots of words here, Paul. Very good of you to stick with it. And many apologies if I have made the process longer than necessary.

Many thanks for getting this far.
Cheers paul

 

[macropod]

Hi sinbad,

1. So, are you saying that what you're starying with is nothing more than titles, like:
the way of an eagle ethel mdell
with spaces (not underscores), no '.epub' or anything else? If so, that's markedly different to what you've described before.

2. No problem.

3. As for inserting 'by', that's not a practical proposition - there's no way to determine where it should go.

Cheers
Paul Edstein
[MS MVP - Word]

 

[sinbad99]

Sorry, Paul, I hope I've not confused you.

To show the title, and to have a link to the ebook, I need to code where the link is - which as we know is ancient-classics/. That's the folder the ebook is in.

Then we give the name of the file. That's '/the_way_of_an_eagle_ethel_mdell.epub"

Together, that is coded as '<a href="ancient_classics/the_way_of_an_eagle_ethel_mdell.epub"

Within the angle brackets, we include a title-tag, which shows when the cursor hovers over it. But we do it in this situation mainly so that Search Engine robots will note it.

So the complete CODE side of linking to the ebook is '<a href="ancient_classics/the_way_of_an_eagle_ethel_mdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.">

Next we add the title that you actually see on the webpage, which is 'The Way of an Eagle by Ethel Mdell'

Then we finish off the code with an '</a>'

To put the following entry on a separate line, we add <br />

And the whole thing, for each ebook is '<a href="ancient_classics/TheWayOfAnEagleEthelMdell.epub" title="New free eBooks on eBookTrove-dot-com, and being added to all the time.">The Way of an Eagle Ethel Mdell </a><br />'

The link can be done the VBA way, or with underscores in place of spaces.

I hope this makes it clear to you. Sorry that it is complicated. Many thanks for getting this far with it.

 

[macropod]

Hi sinbad,

That's all very well, but you haven't answered my question about what the text is before you start processing. Is it like:
the way of an eagle ethel mdell
or is is like:
the way of an eagle ethel mdell.epub
or is it something else? If so, what? Give some examples.

If you don't say what the starting point is, you can't expect anyone to code the macro correctly.

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Hi sinbad,

Try the following macro. Since a macro can't can't tell where 'by' should go, the macro highlights any strings that don't already have 'by' in them. You will need to add it to those strings. You will also need to check the other results, as 'by' may be in the title and not before the author's name.

Sub ByTest()
Application.ScreenUpdating = False
Dim StrBkTrv As String, StrStrt As String, StrEnd As String, StrTmp As String, StrTtl As String
StrBkTrv = ".epub"" title=""New free eBooks on eBookTrove-dot-com, and being added to all the time."">"
StrStrt = "<a href=""ancient_classics/"
StrEnd = "</a><br />"
With ActiveDocument.Range
  With .Find
    .MatchWildcards = True
    .ClearFormatting
    .Replacement.ClearFormatting
    .Wrap = wdFindStop
    .Execute Replace:=wdReplaceAll
    .Text = "[!^13]{1,}"
    .Replacement.Text = ""
    .Execute
  End With
  Do While .Find.Found
    StrTmp = Split(.Text, ".epub")(0)
    StrTmp = Trim(Replace(StrTmp, "_", " "))
    If InStr(StrTmp, "by ") = 0 Then .HighlightColorIndex = wdYellow
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

Once you'd fixed up all the 'by' entries, run the following macro. Since I don't really know what you're starting point is, I've coded for a number of possibilities.

Most of the work is done by the ProperCase function. It took a lot of work to develop this!! If the list of exclusions from the upper-case conversion is too broad, you can delete the ones you don't want. Alternatively you can add more. 'Surnames like O', Mc & Mac and hyphenated names are converted to proper case. If there are other kinds you need to treat in the same way, you'll need to go through the output - or add to the function's routines. You'll still need to check the output, though, as there may be some scenarios I've missed.

Sub AddTags()
Application.ScreenUpdating = False
Dim StrBkTrv As String, StrStrt As String, StrEnd As String, StrTmp As String, StrTtl As String
StrBkTrv = ".epub"" title=""New free eBooks on eBookTrove-dot-com, and being added to all the time."">"
StrStrt = "<a href=""ancient_classics/"
StrEnd = "</a><br />"
With ActiveDocument.Range
  With .Find
    .MatchWildcards = True
    .ClearFormatting
    .Replacement.ClearFormatting
    .Wrap = wdFindStop
    .Execute Replace:=wdReplaceAll
    .Text = "[!^13]{1,}"
    .Replacement.Text = ""
    .Execute
  End With
  Do While .Find.Found
    StrTmp = Split(.Text, ".epub")(0)
    StrTmp = Trim(Replace(StrTmp, "_", " "))
    While Right(StrTmp, 1) = "."
      StrTmp = Left(StrTmp, Len(StrTmp) - 1)
    Wend
    While InStr(StrTmp, "  ") > 0
      StrTmp = Replace(StrTmp, "  ", " ")
    Wend
    StrTmp = Replace(StrTmp, " ", "_")
    StrTtl = ProperCase(StrTxt:=Replace(StrTmp, "_", " "), bCaps:=False, bExcl:=False)
    .Text = StrStrt & StrTmp & StrBkTrv & StrTtl & StrEnd
    If InStr(StrTtl, "by ") = 0 Then .HighlightColorIndex = wdYellow
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

Function ProperCase(StrTxt As String, Optional bCaps As Boolean, Optional bExcl As Boolean) As String
'Convert an input string to proper-case.
'Surnames like O', Mc & Mac and hyphenated names are converted to proper case also.
'If bCaps = False, then upper-case strings like ABC are preserved; otherwise they're converted.
'If bExcl = False, words in the exclusion list are retained as lower-case, unless after specified punctuation marks.
Dim i As Long, j As Long, k As Long, l As Long, bFnd As Boolean
Dim StrChr As String, StrExcl As String, StrMac As String, StrPunct As String, StrTmpA As String, StrTmpB As String
'exclusion list
StrExcl = " a , an , and , as , at , but , by , for , from , if , in , is , of , on , or , the , this , to , with "
' Mac name lower-case list
StrMac = "Macad,Macau,Macaq,Macaro,Macass,Macaw,Maccabee,Macedon,Macerate,Mach,Mack,Macle,Macrame,Macro,Macul,Macumb"
StrPunct = "!,:,.,?,"""
If bExcl = True Then
  StrExcl = ""
  StrPunct = ""
End If
If Len(Trim(StrTxt)) = 0 Then
  ProperCase = StrTxt
  Exit Function
End If
If bCaps = True Then StrTxt = LCase(StrTxt)
StrTxt = " " & StrTxt & " "
For i = 1 To UBound(Split(StrTxt, " "))
  StrTmpA = " " & Split(StrTxt, " ")(i) & " "
  StrTmpB = UCase(Left(StrTmpA, 2)) & Right(StrTmpA, Len(StrTmpA) - 2)
  StrTxt = Replace(StrTxt, StrTmpA, StrTmpB)
Next i
StrTxt = Trim(StrTxt)
'Code for handling O' names
For i = 1 To UBound(Split(StrTxt, "'"))
  If InStr(Right(Split(StrTxt, "'")(i - 1), 2), " ") = 1 Or _
    Right(Split(StrTxt, "'")(i - 1), 2) = Right(Split(StrTxt, "'")(i - 1), 1) Then
    StrTmpA = Split(StrTxt, "'")(i)
    StrTmpB = UCase(Left(StrTmpA, 1)) & Right(StrTmpA, Len(StrTmpA) - 1)
    StrTxt = Replace(StrTxt, StrTmpA, StrTmpB)
  End If
Next
'Code for handling hyphenated names
For i = 1 To UBound(Split(StrTxt, "-"))
  StrTmpA = Split(StrTxt, "-")(i)
  StrTmpB = UCase(Left(StrTmpA, 1)) & Right(StrTmpA, Len(StrTmpA) - 1)
  StrTxt = Replace(StrTxt, StrTmpA, StrTmpB)
Next
'Code for handling names starting with Mc
If Left(StrTxt, 2) = "Mc" Then
  Mid(StrTxt, 3, 1) = UCase(Mid(StrTxt, 3, 1))
End If
i = InStr(StrTxt, " Mc")
If i > 0 Then
  Mid(StrTxt, i + 3, 1) = UCase(Mid(StrTxt, i + 3, 1))
End If
'Code for handling family names starting with Mac
If Left(StrTxt, 3) = "Mac" Then
  bFnd = False
  For j = 0 To UBound(Split(StrMac, ","))
    If InStr(Split(StrTxt, " ")(0), Split(StrMac, ",")(j)) > 0 Then
      bFnd = True
      Exit For
    End If
  Next
  If bFnd = False Then
    If Len(Split(Trim(StrTxt), " ")(0)) > 4 Then
      Mid(StrTxt, 4, 1) = UCase(Mid(StrTxt, 4, 1))
    End If
  End If
End If
i = InStr(StrTxt, " Mac")
If i > 0 Then
  If Len(StrTxt) > i + 4 Then
    bFnd = False
    For j = 0 To UBound(Split(StrMac, ","))
      If InStr(Split(Mid(StrTxt, i + 1, Len(StrTxt) - i - 1), " ")(0), Split(StrMac, ",")(j)) > 0 Then
        bFnd = True
        Exit For
      End If
    Next
    If bFnd = False Then
      Mid(StrTxt, i + 4, 1) = UCase(Mid(StrTxt, i + 4, 1))
    End If
  End If
End If
'Code to restore excluded words to lower case
For i = 0 To UBound(Split(StrExcl, ","))
  StrTmpA = Split(StrExcl, ",")(i)
  StrTmpB = UCase(Left(StrTmpA, 2)) & Right(StrTmpA, Len(StrTmpA) - 2)
  If InStr(StrTxt, StrTmpB) > 0 Then
    StrTxt = Replace(StrTxt, StrTmpB, StrTmpA)
    'Make sure an excluded words following punctution marks are given proper case anyway
    For j = 0 To UBound(Split(StrPunct, ","))
      StrChr = Split(StrPunct, ",")(j)
      StrTxt = Replace(StrTxt, StrChr & StrTmpA, StrChr & StrTmpB)
    Next
  End If
Next
ProperCase = StrTxt
End Function

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Corrections to the first macro -
change:

Application.ScreenUpdating = False
Dim StrBkTrv As String, StrStrt As String, StrEnd As String, StrTmp As String, StrTtl As String
StrBkTrv = ".epub"" title=""New free eBooks on eBookTrove-dot-com, and being added to all the time."">"
StrStrt = "<a href=""ancient_classics/"
StrEnd = "</a><br />"
With ActiveDocument.Range

to:

Application.ScreenUpdating = False
Dim StrTmp As String
With ActiveDocument.Range

and change:

If InStr(StrTmp, "by ") = 0 Then .HighlightColorIndex = wdYellow

to:

If InStr(StrTmp, " by ") = 0 Then .HighlightColorIndex = wdYellow

Cheers
Paul Edstein
[MS MVP - Word]

 

[sinbad99]

Macropod for President! This looks amazing, Paul, and a lot of hard mental work. I should be able to try this out in an hour or so. I'll report back straight away. Phew, this is grown-up coding. No wonder Mr Gates sees you as a Most Valuable Professional. Thank you. Back soon.