ExcelBanter - trying to find random drive letters

VBAfunkymonk wrote:
hi i am tryting to save a file on a sharded drive how ever as other
people will be using it and they have different dirve letters is
there a way I can find what their dirve letter is and then specify
what the drive letter is in my code??

Use the UNC path. If you need to convert, this function will do it:

' API call used for UNCDOSUNC conversions...
Private Declare Function WNetGetConnectionA Lib "MPR.DLL" (ByVal _
LocalName As String, ByVal RemoteName As String, _
RetLength As Long) As Long
Function UNCPathToDOSPath(UNCName As String) As String
'================================================= ==================
'= Procedu UNCPathToDOSPath =
'= Type: Function =
'= =
'= Purpose: Converts a UNC (Servername\Drive name) path into a =
'= DOS (drive letter) path. =
'= Parameters: UNCName - String - The UNC path/name to convert. =
'= Returns: String - The converted DOS name. An empty string =
'= if the UNC path was not available. =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |28-Aug-01| Robert Bruce | Created =
'================================================= ==================

Dim lngMaxLen As Long
Dim strLocalName As String
Dim strRemoteName As String
Dim lngCount As Long
Dim lngGetConRet As Long

' Did we get a DOS rather than a UNC path?..
If Left(UNCName, 2) Like "?:" Then
UNCPathToDOSPath = UNCName
Else

'Loop through drive letters D to Z...
For lngCount = 68 To 90

' Max length of file path...
lngMaxLen = 8192

' Seed drive letter...
strLocalName = Chr(lngCount) & ":"

' Create Buffer for remote name...
strRemoteName = Space(lngMaxLen)

' Feed drive letter into API function
lngGetConRet = WNetGetConnectionA(strLocalName, strRemoteName, _
lngMaxLen)

' API call returns Zero on success...
If lngGetConRet = 0 Then
' Double-check return string - should NOT be just
' a null char...
If InStr(1, strRemoteName, vbNullChar) 1 Then
' Get the portion of the return string up to
' the null character...
strRemoteName = Left(strRemoteName, InStr(1, _
strRemoteName, vbNullChar) - 1)
' Quick test to see of the remote name of the drive
' is longer than our UNC string - no need to carry
' on
' if it is...
If Len(UNCName) Len(strRemoteName) Then
' Check to see if the UNC name begins with the
' remote name of the drive we found...
If Left(UCase(UNCName), Len(strRemoteName)) = _
UCase(strRemoteName) Then
' Strip the remote name off the UNC Name...
strRemoteName = Right(UNCName, _
Len(UNCName) - Len(strRemoteName))
' ...and add the drive letter...
strRemoteName = strLocalName & strRemoteName
' Return the finished string...
UNCPathToDOSPath = strRemoteName
' No need to continue processing...
Exit For
End If
End If
End If
End If
Next

' If no matching UNC Path found then return empty string
If lngCount = 91 Then UNCPathToDOSPath = ""

End If
End Function

--
Rob

Please keep conversations in the newsgroup so that all may contribute
and benefit.