为不同用户返回 VBA 网络驱动器号

问题描述

我为自己和同事创建了一个门户，用于下载引用不同网络驱动器的工作簿模板以进行整合.

I've created a portal for myself and coworkers to download workbook templates that reference different network drives in an effort to consolidate.

门户托管在会计"(Z:/) 驱动器中，但其中一个工作簿引用了不同驱动器BI"(Y:/) 中的电子表格.

The portal is hosted in the "Accounting" (Z:/) drive, but one of the workbooks references a spreadsheet in a different drive "BI" (Y:/).

这在我的机器上运行良好，但我的同事有不同的驱动器号(例如 M:/Accounting、U:/BI).

This works splendidly on my machine, but my coworkers have different drive letters (e.g. M:/Accounting, U:/BI).

有没有办法在网络中搜索名称并返回驱动器号?

Is there a way to search a network for a name and return the drive letter?

这是我的代码的粗略近似:

Here's a rough approximation of my code:

Option Explicit

Sub mySub()


dim WBaPath as String
dim WBbPath as String
WBaPath = "Y:BI-AccountingmyWorkbook.xlsm"
WBbPath = "Z:PortalmyOtherWorkbook.xlsm"
dim WBa as Workbook
dim WBb as Workbook
set WBa = Workbooks.open(WBaPath)
set WBb = ThisWorkbook

'Code to do stuff


End Sub

文件夹和文件将具有相同的名称，但要确保我部门中的所有用户都可以使用这些工具而无需重新编程，最好不必在运行时进行选择，最好的方法是什么?

The folders and files will have the same name, but what is the best way to make sure that all users in my department can use these tools without having to reprogram and, preferably, without having to choose at run-time?

推荐答案

您可以使用 FileSystemObject 的 Drives 属性 来实现这个:

You can use the FileSystemObject's Drives property to achieve this:

Function FindDriveLetter(shareToFind As String) As String
    Dim fs As Object
    Dim dc As Object
    Dim d As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set dc = fs.Drives
    For Each d In dc
        If d.drivetype = 3 Then
            If UCase(d.sharename) = UCase(shareToFind) Then
                FindDriveLetter = d.driveletter
                Exit Function
            End If
        End If
    Next
    FindDriveLetter = ""
End Function

Sub Test()
    MsgBox FindDriveLetter("\SERVER01SHAREXYZFILES")
    ' use whatever share name you have mapped to your "Y" or "Z" drive
    ' instead of "\SERVER01SHAREXYZFILES"
End Sub

但使用 UNC 路径(例如 WBaPath = "\SERVER01SHAREXYZFILESBI-AccountingmyWorkbook.xlsm")通常更容易.

这篇关于为不同用户返回 VBA 网络驱动器号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！