UNC Filepath from File Selector Dialog (1 Viewer)

mhorner

Registered User.
Local time
Today, 19:36
Joined
May 24, 2018
Messages
50
Hi everybody, I built and manage a compliance database for my company. Instead of actually attaching PDFs to the records, to keep size down -- I've added a hyperlink text box to hold a hyperlink to the file. I have a command button (cmdFileDialog) which brings a file selector up and lets the user navigate to and select a file. The file path is populated into the text box txtFileHyperlink.

The issue is, the path is a mapped network path (for example R:/path/path/file.pdf). Users have the drive mapped differently, so I'm trying to have the file selector extract the UNC path instead -- or may convert the path to UNC.

Does anybody have any experience or guidance on how I might modify the code below to achieve this?

Any input is greatly appreciated.

Code:
Private Sub cmdFileDialog_Click()
Dim f As Object

Set f = Application.FileDialog(3)

f.AllowMultiSelect = False

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        sFile = Filename(f.SelectedItems(i), sPath)
        txtFileHyperlink = sPath & sFile
    Next
End If
End Sub

Public Function Filename(ByVal strPath As String, sPath) As String

    sPath = Left(strPath, InStrRev(strPath, "\"))
    Filename = Mid(strPath, InStrRev(strPath, "\") + 1)
    
End Function
 

isladogs

MVP / VIP
Local time
Today, 23:36
Joined
Jan 14, 2017
Messages
18,186
To get the UNC path, browse to the file in Explorer using the Network location, even if its on your own hard drive
 

mhorner

Registered User.
Local time
Today, 19:36
Joined
May 24, 2018
Messages
50
Thank you -- but I don't think this achieves my objective, unless I misunderstood your comment.

I have a file dialog module (code listed above) embedded into my access form, called by a command button. I'm looking to modify the VBA code above to instead populate the UNC path into txtFileHyperlink.

Any guidance on how to achieve this would be appreciate.
 

mhorner

Registered User.
Local time
Today, 19:36
Joined
May 24, 2018
Messages
50
Thanks, DBGuy -- I appreciate the help. It appears I'm in over my head. I'm not sure how to implement this code into my existing file selector code. But I'm making progress. If I can figure this out, I'll post my solution.

I got the function to convert the mapped drive letter "R:" into a UNC path with the code you sent. Awesome first step.

Code:
Private Sub cmdFileSelector_Click()
MsgBox GETNETWORKPATH("R:")
End Sub

Public Function GETNETWORKPATH(ByVal DriveName As String) As String
    Dim objNtWork   As Object
    Dim objDrives   As Object
    Dim lngLoop     As Long
    
    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives
    
    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GETNETWORKPATH = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next
End Function

So now, if a user opens the file selector, navigates to and selects a file - the file path is populated into txtFileHyperlink.

I suppose I need to find a way now to replace the mapped drive letter from the file path in txtFileHyperlink with the UNC path using the code you sent.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:36
Joined
Oct 29, 2018
Messages
21,358
Thanks, DBGuy -- I appreciate the help. It appears I'm in over my head. I'm not sure how to implement this code into my existing file selector code. But I'm making progress. If I can figure this out, I'll post my solution.

I got the function to convert the mapped drive letter "R:" into a UNC path with the code you sent. Awesome first step.

Code:
Private Sub cmdFileSelector_Click()
MsgBox GETNETWORKPATH("R:")
End Sub

Public Function GETNETWORKPATH(ByVal DriveName As String) As String
    Dim objNtWork   As Object
    Dim objDrives   As Object
    Dim lngLoop     As Long
    
    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives
    
    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GETNETWORKPATH = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next
End Function
So now, if a user opens the file selector, navigates to and selects a file - the file path is populated into txtFileHyperlink.

I suppose I need to find a way now to replace the mapped drive letter from the file path in txtFileHyperlink with the UNC path using the code you sent.
Hi. If txtFileHyperlink contains something like:


S:\NetworkFolder\Subfolder\Filename.ext


Then, using

Code:
Left(txtFileHyperlink,2)
should return "S:", which you can then use for the other code to find the equivalent UNC path.
 

moke123

AWF VIP
Local time
Today, 19:36
Joined
Jan 11, 2013
Messages
3,852
here's another

Code:
Function GetUNC(strMappedDrive As String) As String

    Dim objFso As FileSystemObject
    Set objFso = New FileSystemObject
    Dim strDrive As String
    Dim strShare As String

    'Separated the mapped letter from
    'any following sub-folders
    strDrive = objFso.GetDriveName(strMappedDrive)

    'find the UNC share name from the mapped letter
    strShare = objFso.Drives(strDrive).ShareName

    'The Replace function allows for sub-folders
    'of the mapped drive
    GetUNC = Replace(strMappedDrive, strDrive, strShare)

    Set objFso = Nothing    'Destroy the object

End Function
 

mhorner

Registered User.
Local time
Today, 19:36
Joined
May 24, 2018
Messages
50
Hi. If txtFileHyperlink contains something like:


S:\NetworkFolder\Subfolder\Filename.ext


Then, using

Code:
Left(txtFileHyperlink,2)
should return "S:", which you can then use for the other code to find the equivalent UNC path.


So helpful, thank you greatly. Here is the final solution. High level overview:
  1. First opens a file selector to allow the user to select a file
  2. Second, the filepath of the selected file is stored into variable "RawHyperlink"
  3. Next it extracts the drive letter and stores it to variable "DriveLetter"
  4. The remaining subfolders and filename are stored to variable "FolderFile"
  5. It converts the value in DriveLetter to a UNCPath using the GETNETWORKPATH module
  6. Finally it merges the UNCPath and FolderFile back into a usable hyperlink, and populates it into a clickable textbox "TxtFileHyperlink"



Code:
Private Sub cmdFileDialog_Click()
Dim f As Object
Dim RawHyperlink As String

Set f = Application.FileDialog(3)

f.AllowMultiSelect = False

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        sFile = Filename(f.SelectedItems(i), sPath)
        RawHyperlink = sPath & sFile
        DriveLetter = Left(RawHyperlink, 2)
        FolderFile = Mid(RawHyperlink, 3)
        UNCPath = GETNETWORKPATH(DriveLetter)
        txtFileHyperlink = UNCPath & FolderFile
        Next
End If
End Sub

Public Function Filename(ByVal strNetPath As String, sNetPath) As String

    sNetPath = Left(strNetPath, InStrRev(strNetPath, "\"))
    Filename = Mid(strNetPath, InStrRev(strNetPath, "\") + 1)
    
End Function

Private Sub cmdFileSelector_Click()
MsgBox GETNETWORKPATH(txtNetworkDriveLetter)
End Sub


Public Function GETNETWORKPATH(ByVal DriveName As String) As String
    
    Dim objNtWork   As Object
    Dim objDrives   As Object
    Dim lngLoop     As Long
    
    
    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives
    
    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GETNETWORKPATH = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next

End Function

Thanks again for the help getting this running.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:36
Joined
Oct 29, 2018
Messages
21,358
So helpful, thank you greatly. Here is the final solution. High level overview:
  1. First opens a file selector to allow the user to select a file
  2. Second, the filepath of the selected file is stored into variable "RawHyperlink"
  3. Next it extracts the drive letter and stores it to variable "DriveLetter"
  4. The remaining subfolders and filename are stored to variable "FolderFile"
  5. It converts the value in DriveLetter to a UNCPath using the GETNETWORKPATH module
  6. Finally it merges the UNCPath and FolderFile back into a usable hyperlink, and populates it into a clickable textbox "TxtFileHyperlink"
...


Thanks again for the help getting this running.
Hi. Glad to hear you got it sorted out. We were all happy to assist. Good luck with your project.
 

rdab100

New member
Local time
Today, 23:36
Joined
Aug 25, 2022
Messages
14
So helpful, thank you greatly. Here is the final solution. High level overview:
  1. First opens a file selector to allow the user to select a file
  2. Second, the filepath of the selected file is stored into variable "RawHyperlink"
  3. Next it extracts the drive letter and stores it to variable "DriveLetter"
  4. The remaining subfolders and filename are stored to variable "FolderFile"
  5. It converts the value in DriveLetter to a UNCPath using the GETNETWORKPATH module
  6. Finally it merges the UNCPath and FolderFile back into a usable hyperlink, and populates it into a clickable textbox "TxtFileHyperlink"



Code:
Private Sub cmdFileDialog_Click()
Dim f As Object
Dim RawHyperlink As String

Set f = Application.FileDialog(3)

f.AllowMultiSelect = False

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        sFile = Filename(f.SelectedItems(i), sPath)
        RawHyperlink = sPath & sFile
        DriveLetter = Left(RawHyperlink, 2)
        FolderFile = Mid(RawHyperlink, 3)
        UNCPath = GETNETWORKPATH(DriveLetter)
        txtFileHyperlink = UNCPath & FolderFile
        Next
End If
End Sub

Public Function Filename(ByVal strNetPath As String, sNetPath) As String

    sNetPath = Left(strNetPath, InStrRev(strNetPath, "\"))
    Filename = Mid(strNetPath, InStrRev(strNetPath, "\") + 1)
   
End Function

Private Sub cmdFileSelector_Click()
MsgBox GETNETWORKPATH(txtNetworkDriveLetter)
End Sub


Public Function GETNETWORKPATH(ByVal DriveName As String) As String
   
    Dim objNtWork   As Object
    Dim objDrives   As Object
    Dim lngLoop     As Long
   
   
    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives
   
    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GETNETWORKPATH = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next

End Function

Thanks again for the help getting this running.
This UNC rather than mapped file path letter is what I had been looking for, saved me lots of time.
 

Users who are viewing this thread

Top Bottom