Server name in Excel file path

[Guest]

Is it possible to get the full server name in the file path as opposed to
just the drive letter that it refers to?

For example, if I use

ActiveWorkbook.FullName

I get something like this:

L:\test.xls

The full server name for the drive letter L is:

mcssrv02\finserv

I would like the file path to read:

mcssrv02\finserv\test.xls

Thanks for your help.

Kim Mansfield

 

[Bob Phillips]

Here's a simple function to get the UNC path

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

Function GetUNCPath(myDriveLetter As String) As String

Dim lReturn As Long
Dim szBuffer As String

myDriveLetter = Left(myDriveLetter, 1) & ":"

szBuffer = String$(256, vbNullChar)
lReturn = WNetGetConnectionA(myDriveLetter, szBuffer, 256)

If lReturn = 0 Then
GetUNCPath = Left$(szBuffer, InStr(szBuffer, vbNullChar))
Else
GetUNCPath = "Invalid drive"
End If

End Function


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

John,

The full UNC may be exposed through the FSO (FileSystem Scripting Object),
but I'm not certain what method/property it is derived from.

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;192689
HOWTO: Get UNC Path From a Mapped Network Share's Drive Letter


http://support.microsoft.com/default.aspx?scid=kb;en-us;160529
VBA: Sample Code to Return the UNC Path of a Network Drive

 

[Guest]

Thanks Bob. I appreciate your help.

Here's a simple function to get the UNC path

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

Function GetUNCPath(myDriveLetter As String) As String

Dim lReturn As Long
Dim szBuffer As String

myDriveLetter = Left(myDriveLetter, 1) & ":"

szBuffer = String$(256, vbNullChar)
lReturn = WNetGetConnectionA(myDriveLetter, szBuffer, 256)

If lReturn = 0 Then
GetUNCPath = Left$(szBuffer, InStr(szBuffer, vbNullChar))
Else
GetUNCPath = "Invalid drive"
End If

End Function


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)