Getting Hyperlink Address - Excel 2003 vba

[Pm1995]

Hi all,

Please Help.
I am trying to retrieve the address (file path) from a Hyperlink in a cell using vba.

 

[ravi4ever]

try this code..

cellAddress = Replace(Range("A1").Hyperlinks(1).Address, "mailto:", "")

 

[Pm1995]

Thanks Ravi,

That worked but it only gave me the file name.

eg.
myfile.xls

How do I get the full file path.

e.g.
\\server05\idept$\BusServ\security\myfile.xls

 

[ravi4ever]

it should give your the full path with filename.. Create a hyperlink on A1 then run this code, it would show you the full path in a message box and will also update the same path at B1..

Sub GetCellAddress()
    cellAddress = Replace(Range("A1").Hyperlinks(1).Address, "mailto:", "")
    MsgBox cellAddress
    Cells(1, 2) = cellAddress
End Sub

 

[Pm1995]

I can see what has happened.
When the hyperlink is created the address field shows the full path.
When the workbook is saved the field name becaomes just the file name.

Do you know how I can fix this?

 

[Biz]

Try Formula below

=fnUNCPath(LEFT(INFO("directory"),1))

Copy and paste Custom Funtion in Macro Module

 '// 32-bit Function version.
 '// Note:
Declare Function WNetGetConnection32 _
Lib "mpr.dll" _
Alias "WNetGetConnectionA" ( _
ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
lSize As Long) _
As Long
 
 '// 32-bit declarations:
Dim lpszRemoteName As String
Dim lSize As Long
 
 '// Use for the return value of WNetGetConnection() API.
Const NO_ERROR As Long = 0
 
 '// The size used for the string buffer. Adjust this if you
 '// need a larger buffer.
Const lBUFFER_SIZE As Long = 1052
 
Function fnUNCPath(strDriveLetter As String) As String
     '// Takes specified Local Drive Letter
     '// eg E,D,H Etc and converts to UNC
     
    Dim cbRemoteName As Long
    Dim lStatus As Long
     
     '// Add a colon to the drive letter entered.
    strDriveLetter = Left(strDriveLetter, 1) & ":"
     
     '// Specifies the size in charaters of the buffer.
    cbRemoteName = lBUFFER_SIZE
     
     '// Prepare a string variable by padding spaces.
    lpszRemoteName = lpszRemoteName & Space(lBUFFER_SIZE)
     
     '// Return the UNC path (eg.\\Server\Share).
    lStatus = WNetGetConnection32( _
    strDriveLetter, _
    lpszRemoteName, _
    cbRemoteName)
     
     '// Has WNetGetConnection() succeeded.
     '// WNetGetConnection()returns 0 (NO_ERROR)
     '// if it succesfully retrieves the UNC path.
    If lStatus = NO_ERROR Then
         '// Get UNC path.
        fnUNCPath = lpszRemoteName
    Else
         '// Unable to obtain the UNC path.
        fnUNCPath = "NO UNC path"
    End If
     
End Function

Does it help?

Biz

 

[Pm1995]

Thanks Biz,

My problem is not converting a path with a drive letter to a UNC or visa versa.

For some reason only the filename is returned rather than the full path (unc or other). I can't seem to work out why.

 

[ravi4ever]

I read other threads with similar problem with UNC.. Got this solution, try it..

In Excel go to Tools> Options> General> Web Options> Files, un-check "Update links on save" ..

 

[Pm1995]

Thanks Ravi,
It worked,
You are a champion almost as good as Kohli's 133 last night.

 

[ravi4ever]

Glad to know that it was of help.. yes that match made so much of exitment in the last 10 overs..