I was asked today about using VBA to convert a mapped network drive letter to a UNC path. Say for example we have drive Z: mapped to \\Server\Folders\Accounts. How can we use VBA to return the UNC data when we only know the drive letter? The answer is to use the code shown below. This does require us to set a reference in the VB Editor using Tools, References to the Microsoft Scripting Runtime.
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
So, using the example mapping stated earlier, if I call GetUNC(“Z:”) the function returns
\\Server\Folders\Accounts and if I call GetUNC(“Z:\OldData”) then it will return