Updating existing Hyperlinks

[hairwormman]

I am trying to mass update existing hyperlink fields. The new information is in Excel (which is how the new data is coming to me). When I do this, thorugh an Update query, the "text to display" field within the hyperlink changes, but the "address" field does not. How can I change the "address" field.
Thanks for your time!!

Cheers...Ben

 

[striker83716]

Here is a method, in your excel file create a new module and paste the following code; This code I found at

http://www.techonthenet.com

Function HyperLinkText(pRange As Range) As String

Dim ST1 As String
Dim ST2 As String
Dim LPath As String
Dim ST1Local As String

If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If

LPath = ThisWorkbook.FullName

ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress

If Mid(ST1, 1, 15) = "..\..\..\..\..\" Then
ST1Local = ReturnPath(LPath, 5) & Mid(ST1, 15)
ElseIf Mid(ST1, 1, 12) = "..\..\..\..\" Then
ST1Local = ReturnPath(LPath, 4) & Mid(ST1, 12)
ElseIf Mid(ST1, 1, 9) = "..\..\..\" Then
ST1Local = ReturnPath(LPath, 3) & Mid(ST1, 9)
ElseIf Mid(ST1, 1, 6) = "..\..\" Then
ST1Local = ReturnPath(LPath, 2) & Mid(ST1, 6)
ElseIf Mid(ST1, 1, 3) = "..\" Then
ST1Local = ReturnPath(LPath, 1) & Mid(ST1, 3)
Else
ST1Local = ST1
End If

If ST2 <> &quot;&quot; Then
ST1Local = &quot;[&quot; & ST1Local & &quot;]&quot; & ST2
End If

HyperLinkText = ST1Local

End Function


Function ReturnPath(pAppPath As String, pCount As Integer) As String

Dim LPos As Integer
Dim LTotal As Integer
Dim LLength As Integer

LTotal = 0
LLength = Len(pAppPath)

Do Until LTotal = pCount + 1
If Mid(pAppPath, LLength, 1) = &quot;\&quot; Then
LTotal = LTotal + 1
End If
LLength = LLength - 1
Loop

ReturnPath = Mid(pAppPath, 1, LLength)

End Function

Next... name your range of cells in Excel that has the hyperlink as &quot;prange&quot; then in the cells next to your hyperlinks enter &quot;=HyperLinkText(A1)&quot; A1 being your cell with the link.

This will display the hyperlink address next to your &quot;hyperlink&quot; in excel.

Now when you import you spreadsheet into access you will have 2 fields, the display name and the address.

Access stores hyperlinks as &quot;displaytext#address#subaddress#screentip&quot;

So at this point create your query that updates your table ie.. [display name] & &quot;#&quot; & [address] and have this append to your table hyperlink field.

I was able to make this work, but this method requires a bit of code for each excel spreadsheet that you have. Of course once you build your module in Excel you can import/export it to whichever excel workbook you need it in.

Good luck (-: