Return network share/drive letter equivalent

[XP]

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.

[Jim Thomlinson]

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.

[Jim Thomlinson]

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.