How to write a file path for two people mapped to different drives

Question

i'm trying to write a code that saves a back up of an excel spreadsheet to the same shared folder, mapped to two different letters, only when certain users are saving. this is a file many people access, so I'd like to save a back up for only when myself or my coworker is in it.

I have the folder mapped to the G drive, my coworker has it on the I drive. How can I write the filepath to read both of our drives?

TIA!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Environ("Username") <> "agene" And Environ("Username") <> "aking" Then
        Exit Sub
    End If
    With ThisWorkbook
        .SaveCopyAs ("\Excel\Backup\Backup of " & .Name)
    End With
End Sub

Write the file directly to the UNC path (\\servername\folder\folder2\) - don't use the mapped drive letter. — Tim Williams, Jan 25 '19 at 21:06
To find out the UNC path, as @TimWilliams writes, you can execute the `net use` command at a Windows command prompt. — Marc, Jan 25 '19 at 21:29
Note - I'm not suggesting you don't map the drive at all, just to use the UNC path for cases where the drive letter is unknown — Tim Williams, Jan 25 '19 at 21:48

score 0 · Answer 1 · answered Jan 25 '19 at 21:07

Network paths are tricky. I created a function for my own library based on another author's work to determine the fully qualified path name to a network drive, given the drive letter. So take the drive letter for each user, determine the full network path, and always use that network path to save the file.

Public Function GetNetworkPath(ByVal driveName As String) As String
    '--- Converts a drive letter, e.g. 'W:\', to its fully qualified network
    '    path useful for saving a network folder location without any user-
    '    specific custom mapping
    '--- from https://www.mrexcel.com/forum/excel-questions/
    '            658830-show-full-file-paths-unc-not-mapped-drive-letters-print.html
    Dim networkObject  As Object
    Dim networkDrives As Object

    Set networkObject = CreateObject("WScript.Network")
    Set networkDrives = networkObject.enumnetworkdrives

    Dim i As Long
    GetNetworkPath = vbNullString
    For i = 0 To networkDrives.count - 1 Step 2
        If UCase$(networkDrives.item(i)) = UCase$(driveName) Then
            GetNetworkPath = networkDrives.item(i + 1)
            Exit For
        End If
    Next
End Function

How to write a file path for two people mapped to different drives

1 Answers1