The example short coding below works OK for me, it will do, but it seems an inefficient roundabout way to do it. That is because I know some Excel VBA, but am less familiar with Word VBA, and could not find an equivalent pure Word VBA way to do it.
So I thought I would ask if anyone better at Word VBA has a better idea?
So what I am doing is highlighting a text in WORD, then running the coding. If that text is a web site listed in the macro, then it tells me the URL.
(In case it helps in the choice of a solution, the fuller picture of the requirement to which this is just the first part, is:
For example if I had in a word text …
Over at Eileenslounge
… then finally I want the text to be changed to make the BB code type thingy for the site:
Over at [url=https://eileenslounge.com/app.php/portal] Eileenslounge [/url]
The lists of URLs against (site) names will likely be added to and also the list will grow to include thing’s like post titles against their URL etc. so eventually the list will be very big. I would probably prefer to have the coding working on a small text selection rather than a large selection or full document
So this first bit is just matching a site name to it’s URL. I can have a go at doing the rest, but mentioned the fuller requirement just in case it makes a particular solution idea for this first bit more appropriate)
Thanks
Alan
Code: Select all
' Select a text in Word of, for example, a web site name, whose URL you want
Sub ExcelVBAMatchInWordVBA() ' ' https://eileenslounge.com/viewtopic.php?f=26&t=40789
Rem 0 The text you selected
Dim SelTxt As String
Let SelTxt = Selection.Text ' A text I highlighted in Word
Rem 1 Two arrays, one with web site names, and the other gives the corresponding URL in the same array position
Dim arrURL() As Variant, arrTxt() As Variant ' These arrays gives me s sort of list of site name agaist the URL link for it
Let arrTxt() = Array("Eileen's Lounge", "eileenslounge", "Excel Fox", "excelfox")
Let arrURL() = Array("https://eileenslounge.com/app.php/portal", "https://eileenslounge.com/app.php/portal", "https://www.excelfox.com/forum/forum.php", "https://www.excelfox.com/forum/forum.php")
Rem 2 A thing to enable me to use Excel VBA functions in Word VBA
Dim XL As Object
Set XL = CreateObject("Excel.Application")
' 2b Get the URL corrsponding to a selectwd web site name using the Excel Match function
Dim MtchRes As Variant
Let MtchRes = XL.match(SelTxt, arrTxt(), 0) ' If SelTxt is found in arrTxt() then the Excel Match function will return the integer "position along" in the array of the match. (The start left is position 1)
If IsError(MtchRes) Then ' If no match is found, then a fatal error does not occur, - instead a vbError is returned, which we can test for ( https://eileenslounge.com/viewtopic.php?p=297474#p297474 )
MsgBox prompt:="No match found"
Else
MsgBox prompt:="The URL you want is " & arrURL(MtchRes - 1) ' -1 is because VBA arrays start at indicia 0, (in other words the start left is position 0)
End If
Set XL = Nothing ' Apparantly this is not needed in VBA, but just to be on the safe side, it does no harm
End Sub