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.

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.

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.