Return network share/drive letter equivalent
 

I am using Office 2003 on Windows XP.

I need some help coding a function that when supplied with a full path and
file name and the drive is a network share, it returns the equivalent drive
letter - if one is mapped - otherwise empty string; if the drive is a mapped
letter, return the equivalent network share. For non-network drives, just
return the drive letter. For example:

1) \\network\share\myData\subfolder\MyFile.xls
Would return: I:\ (assuming \\network\share\ is mapped to letter "I")
BUT, if this is an unmapped network share then an empty string would be
returned

2) I:\myData\subfolder\MyFile.xls
would return: \\network\share\

3) C:\subfolder\MyFile.xls
Would return: C:\

Below is my incomplete start on a function, but I'm having trouble:

Public Function DriveNameEquivalent(argFullName As String) As String
'IF NETWORK SHARE SUPPLIED - RETURNS EQUIV DRIVE LETTER - & - VICE VERSA
Dim oFSO As Object
Dim oDrives As Object
Dim oDrive As Object
Dim strLetter As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDrives = oFSO.drives
For Each oDrive In oDrives
If UCase(oDrive.ShareName) =
UCase(oFSO.GetAbsolutePathName(argFullName)) Then strLetter =
oDrive.DriveLetter: Exit For
Next oDrive

Exit Function

Thanks much in advance for your assistance.

Return network share/drive letter equivalent
 

This gets you the drive mapping from the local drive letter. Not sure if you
can make this work but it might give you a start...
--
HTH...

Jim Thomlinson


"XP" wrote:

I am using Office 2003 on Windows XP.

I need some help coding a function that when supplied with a full path and
file name and the drive is a network share, it returns the equivalent drive
letter - if one is mapped - otherwise empty string; if the drive is a mapped
letter, return the equivalent network share. For non-network drives, just
return the drive letter. For example:

1) \\network\share\myData\subfolder\MyFile.xls
Would return: I:\ (assuming \\network\share\ is mapped to letter "I")
BUT, if this is an unmapped network share then an empty string would be
returned

2) I:\myData\subfolder\MyFile.xls
would return: \\network\share\

3) C:\subfolder\MyFile.xls
Would return: C:\

Below is my incomplete start on a function, but I'm having trouble:

Public Function DriveNameEquivalent(argFullName As String) As String
'IF NETWORK SHARE SUPPLIED - RETURNS EQUIV DRIVE LETTER - & - VICE VERSA
Dim oFSO As Object
Dim oDrives As Object
Dim oDrive As Object
Dim strLetter As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDrives = oFSO.drives
For Each oDrive In oDrives
If UCase(oDrive.ShareName) =
UCase(oFSO.GetAbsolutePathName(argFullName)) Then strLetter =
oDrive.DriveLetter: Exit For
Next oDrive

Exit Function

Thanks much in advance for your assistance.

Return network share/drive letter equivalent
 

Here is the code... :-)

Option Explicit

Declare Function WNetGetConnection Lib "mpr.dll" Alias "WNetGetConnectionA"
( _
ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
ByRef cbRemoteName As Long) As Long

Sub Test()
MsgBox UNCfromLocalDriveName("G")
End Sub

Function UNCfromLocalDriveName(strLocalDrive) As String
'Find UNC from Local path
'i.e. Local drive "F:" = "\\RdaServer3\sys1"
' example of usage: UNCfromLocalDriveName("P") <-Actual Drive Letter
' or UNCfromLocalDriveName(A2) <-Cell reference
'
Dim sLocal As String
Dim sRemote As String * 255
Dim lLen As Long

Application.Volatile

sRemote = String$(255, Chr$(32))

lLen = 255
sLocal = strLocalDrive & ":"

WNetGetConnection sLocal, sRemote, lLen

UNCfromLocalDriveName = Trim(sRemote)

End Function

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

This gets you the drive mapping from the local drive letter. Not sure if you
can make this work but it might give you a start...
--
HTH...

Jim Thomlinson


"XP" wrote:

I am using Office 2003 on Windows XP.

I need some help coding a function that when supplied with a full path and
file name and the drive is a network share, it returns the equivalent drive
letter - if one is mapped - otherwise empty string; if the drive is a mapped
letter, return the equivalent network share. For non-network drives, just
return the drive letter. For example:

1) \\network\share\myData\subfolder\MyFile.xls
Would return: I:\ (assuming \\network\share\ is mapped to letter "I")
BUT, if this is an unmapped network share then an empty string would be
returned

2) I:\myData\subfolder\MyFile.xls
would return: \\network\share\

3) C:\subfolder\MyFile.xls
Would return: C:\

Below is my incomplete start on a function, but I'm having trouble:

Public Function DriveNameEquivalent(argFullName As String) As String
'IF NETWORK SHARE SUPPLIED - RETURNS EQUIV DRIVE LETTER - & - VICE VERSA
Dim oFSO As Object
Dim oDrives As Object
Dim oDrive As Object
Dim strLetter As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDrives = oFSO.drives
For Each oDrive In oDrives
If UCase(oDrive.ShareName) =
UCase(oFSO.GetAbsolutePathName(argFullName)) Then strLetter =
oDrive.DriveLetter: Exit For
Next oDrive

Exit Function

Thanks much in advance for your assistance.