我想在VBA代码中获取活动工作簿的路径。 ActiveWorkbook.Path
做这个
但是
我需要它来检索这样的东西:
\MachineNameShareFolderETCETC2
不是:
S:ETCETC2
其中S:
映射到\MachineNameShareFolder
。我该怎么做?
Dim Drive As String
Drive = Left(ActiveWorkbook.Path, 2)
ActiveWorkbookPath = Replace(ActiveWorkbook.Path, Drive, GetNetworkPath(Drive))
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
在尝试转换filepath或类似的任何垃圾之前,请尝试使用Workbook
对象提供的其他几个属性。我个人在我的所有项目(网络驱动器上托管)中使用ActiveWorkbook.FullName
,而且我从未遇到过问题。
也就是说,如果这种方法不起作用,那么肯定有转换filepath的方法。虽然我更喜欢首先浏览对象的属性(它们往往更可靠),但我对使用函数来解决问题并不抵抗。在这里查看本文:https://pagecommunication.co.uk/2014/07/07/15/vba-to-convert-a-mapped-a-mapped-drive-drive-letter-tleter-to-unc-path/一下通往UNC路径的路径可能会有所帮助。两者之间的唯一区别是前者需要参考Microsoft.scripting.runtime,另一个不得。
Sub yourSub()
strMyFullPath = ConvertToFullPath("S:ETCETC2") 'Call below sub
End Sub
Sub ConvertToFullPath(strShortPath as string) as string
Dim obj1 as object
Set obj1 = CreateObject("Scripting.FileSystemObject")
c = obj1.GetDriveName(strShortPath)
a = obj1.GetDrive(c).ShareName
strFullPath = Replace(strShortPath,c,a)
ConvertToFullPath = strFullPath
End Sub
下面的代码将以您要求的方式识别filepath。
Path = Right(CurDir(), Len(CurDir()) - InStr(CurDir(), "") + 1)