How can I retrieve a complete hyperlink address 2

[Tianjin]

I am working on a small piece code by which all hyperlinks in the workbooks will be verified and see whether or not a file associated to certain hyperlink is existing.
My problem is that the return of code "hyperlinks(1).address" was truncated at left side. So I can use this address to tracing down exsistence of the file.

for example, actual path "\\server01\Doc control\Qc\Customer\file.xls " was retrieved as "\\...\..\Customer\file.xls".

Is there any way I can get complete path from anywhere?

Thanks

TianJin

 

[macropod]

Hi TianJin,

I think you're getting a truncated path because the source file is on the same drive. The following function is one I developed for someone else. It finds the address of a source file referred to in a source cell’s hyperlink, inserting the missing path where appropriate.

Dim Source As Range
Dim Called As Boolean

Public Function HLinkAddr(Source As Range)
Dim HAddr As String
Dim HSubAddr As String
If Called = True And Source.Hyperlinks.Count = 0 Then Exit Function
HAddr = Replace(Source.Hyperlinks(1).Address, "/", "\")
If Trim(HAddr) = "" Then HAddr = ThisWorkbook.FullName
If InStr(HAddr, "..\") Then HAddr = ThisWorkbook.Path & Replace(HAddr, "..\", "")
If InStr(HAddr, ":") = False Then HAddr = ThisWorkbook.Path & "\" & HAddr
HSubAddr = Source.Hyperlinks(1).SubAddress
If HSubAddr = "" Then
	HLinkAddr = Haddr
Else
	HLinkAddr = HAddr & ": " & HsubAddr
End If
End Function

This function can be used in a UDF, where the formula is expressed as:

=HLinkAddr(A1)

or called via code like:

Sub TestHLinkAddr()
Set Source = Range("A1")
Called = True
HLinkAddr Source
If Dir(HAddr, vbNormal) = "" Then
	MsgBox "The hyperlink source file: " & vbCrLf & HAddr & vbCrLf & "does not exist"
Else
	MsgBox "The hyperlink source file: " & vbCrLf & HAddr & vbCrLf & "exists."
End If
Called = False
End Sub

Cheers

[MS MVP - Word]

 

[Tianjin]

Thank you for your valuble break through. you really took away the wood blinding me for years.
You are right, the common portion of pathes from workbook where hyperlink stays and target file is truncated.

Thanks again

TianJin