![]() |
|
#2
|
||||
|
||||
|
You need to process each row of the Worksheet in turn and look for the first unlinked version of the name and process only that one e.g. as follows. Ensure there are no empty cells in the used area of the worksheet, which can be open or closed when the code is run.
Code:
Option Explicit
Const strWB As String = "C:\Path\To\Hyperlinks.xlsx"
Const strSheet As String = "Sheet1"
Sub AddHyperlinks()
Dim oDoc As Document
Dim oRng As Range
Dim Arr() As Variant
Dim i As Long
Dim sName As String, sLink As String
Set oDoc = ActiveDocument
Arr = xlFillArray(strWB, strSheet)
For i = 0 To UBound(Arr, 2)
sName = Arr(0, i)
sLink = Arr(1, i)
Set oRng = oDoc.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(findText:=sName)
If oRng.Hyperlinks.Count = 0 Then
oRng.Hyperlinks.Add oRng, sLink, , , sName
Exit Do
End If
Loop
End With
Next i
lbl_Exit:
Exit Sub
End Sub
Private Function xlFillArray(strWorkbook As String, _
strRange As String) As Variant
'Graham Mayor - http://www.gmayor.com - 24/09/2016
Dim RS As Object
Dim CN As Object
Dim iRows As Long
strRange = strRange & "$]" 'Use this to work with a named worksheet
'strRange = strRange & "]" 'Use this to work with a named range
Set CN = CreateObject("ADODB.Connection")
'Set HDR=NO for no header row
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strRange, CN, 2, 1
With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
| Tags |
| hyperlinks, word macro |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How to use Word Macros to mail merge hyperlinks to the texts in textboxes? | syl3786 | Word VBA | 4 | 03-03-2023 10:43 PM |
Word Macro: How to automatically add hyperlinks to timestamp texts?
|
loklokpanda | Word VBA | 0 | 02-12-2023 09:22 AM |
Formula to add +1 to a sequence
|
Benali | Excel | 6 | 05-05-2020 03:47 AM |
| Vlook up multiple sequence | Cryken | Excel | 4 | 01-31-2017 08:45 AM |
| Sequence problem | Pecoflyer | Excel | 2 | 01-10-2017 09:34 AM |