excel - 如何为映射到不同驱动器的两个人编写文件路径

Question

我正在尝试编写一个代码，将excel电子表格的备份保存到同一个共享文件夹中，映射到两个不同的字母，只有当某些用户正在保存时。这是一个很多人都可以访问的文件，所以我想只在我自己或我的同事在其中时保存备份。

我的文件夹映射到 G 盘，我的同事在 I 盘上。如何编写文件路径来读取我们的两个驱动器？

蒂亚!

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

Answer 1

网络路径很棘手。在给定驱动器号的情况下，我根据另一位作者的工作为我自己的库创建了一个函数，以确定网络驱动器的完全限定路径名。因此，获取每个用户的驱动器号，确定完整的网络路径，并始终使用该网络路径来保存文件。

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