Saving a Hyperlink as a unc network path

[orGAZmic]

Hello All,

My level is beginner but I am very grateful to pr2-eugin for posting some helpful code in a previous post

This concept works perfectly for my project but I know it will fail when I deploy my database as the resulting link is saved as a driveletter:\folder format. Because I have no control on how other users have shared drives mapped I would like someone to help me adjust the code in this sample to save the resulting hyperlink as a unc file path.

The relevant code is

Private Sub CUST_FILE_Click()
    Dim dd As Integer
    If Len(Me.CUST_FILE & vbNullString) = 0 Then
        'If there is a NO HYPERLINK FILES stored, then a prompt to select the file is provided.
        Dim fileDump As FileDialog
        Set fileDump = Application.FileDialog(msoFileDialogOpen)
        dd = fileDump.Show
        If dd = -1 Then
            Dim Yourroute As String
            Dim yourrouteName
            Yourroute = fileDump.SelectedItems(1) 'here goes the route to your document
            yourrouteName = StrReverse(Yourroute)
            yourrouteName = StrReverse(Mid(yourrouteName, 1, InStr(yourrouteName, "\") - 1))
            Me.CUST_FILE = yourrouteName & " # " & Yourroute 'the # sign is very important
            Me.fileChangeBtn.Visible = True
        Else
            Me.CUST_FILE.SetFocus
            Me.fileChangeBtn.Visible = False
        End If
    End If
End Sub

Thanks in advance.

Gary

 

[Estuardo]

G'd afternoon Gary,
If i got i right you want to convert z:\share\folder\file.ext to \\server\share\folder\file.ext right? If so. You can get that info from the Windows registry. Usually that info is stored in HKEY_CURRENT_USER\Network. Knowing that and some string manipulation you can get the UNC path. In my example i won't get in the string process but the core. May be something like this:

Public Function GetUNCPath(strDriveLetter As String) As String
'Add reference to: Windows Script Host Object Model
Dim oKey As WshShell
Dim strKey As String


    Set oKey = CreateObject("WScript.Shell")
    strKey = oKey.RegRead("HKEY_CURRENT_USER\Network\" & strDriveLetter & "\RemotePath")
        
        If VBA.Len(strKey) > 0 Then
            GetUNCPath = strKey
        Else
            GetUNCPath = VBA.vbNullString
        End If
      
End Function

Please notice that this won't work if the same share has several mapped drives at different levels, That's what i said that this procedure needs string manipulation.
Say:
z:\Music = \\MediaServer\Beats\Music

y:\Documentaries = \\MediaServer\Movies\mom\documentaries

You will always need to find in the string the matching pairs...When exist!!

G'd luck

 

[orGAZmic]

Good Morning and hope everyone is well,

Could someone find time to help me further with this please. I have now been trying for a couple of weeks to understand this and educate myself further but I'm simply not experienced enough but.

Esturado's idea will work in principle but integrating his thoughts into my code is proving cumbersome at best.

I'm also perusing this because my understanding this could be useful to other users who work in a randomly mapped drive organisation like ours.

I think everything needed is contained within the thread but if anything is missing please let me know.

Thanks in advance.

Gary.