Without changing your code, in the first column I get the likes of:
=HYPERLINK("C:\Users\Public\Documents\10001.txt"," 10001.txt")
while in the second column I get:
C:\Users\Public\Documents\10001.txt
Clicking on the first column takes me to the correct file, so at the moment I don't understand: "
to ensure that clicking on the link can open the file".
Otherwise, it's not difficult to get the 2nd parameter to look like:
=HYPERLINK("C:\Users\Public\Documents\10000.txt"," C:\Users\Public\Documents\10000.txt")
but as far as I'm aware it won't make any difference to whether it opens the correct file or not.
You can do that by changing:
arr(i, 1) = "=hyperlink(""" & arr(i, 2) & """,""" & arr(i, 1) & """)"
to:
arr(i, 1) = "=hyperlink(""" & arr(i, 2) & """,""" & arr(i, 2) & """)"
another way to get hyperlinks is to add them without the formula, either by changing to:
Code:
With ActiveSheet
.UsedRange.Clear
.Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
r = .Range("a" & Rows.Count).End(3).Row
For rw = 2 To UBound(arr)
.Hyperlinks.Add Anchor:=.Cells(rw, 1), Address:=arr(rw, 2), TextToDisplay:=arr(rw, 2)
Next rw
.Range("a1:f" & r).Borders.LineStyle = xlContinuous
.Range("a1:f" & r).Borders.Weight = xlThin
End With
or:
Code:
With ActiveSheet
.UsedRange.Clear
.Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
r = .Range("a" & Rows.Count).End(3).Row
For Each cll In .Range("A2:A" & r).Cells
.Hyperlinks.Add Anchor:=cll, Address:=cll.Offset(, 1).Value, TextToDisplay:=cll.Offset(, 1).Value
Next cll
.Range("a1:f" & r).Borders.LineStyle = xlContinuous
.Range("a1:f" & r).Borders.Weight = xlThin
End With