Server name in Excel file path

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Mansfield - 23 Jan 2006 15:38 GMT

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

Reply to this Message

Bob Phillips - 23 Jan 2006 16:01 GMT

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)

> Is it possible to get the full server name in the file path as opposed to
> just the drive letter that it refers to?
[quoted text clipped - 18 lines]
>
> Kim Mansfield

Reply to this Message

John Mansfield - 23 Jan 2006 16:20 GMT

Thanks Bob. I appreciate your help.

> Here's a simple function to get the UNC path
>
[quoted text clipped - 51 lines]
> >
> > Kim Mansfield

Reply to this Message

galimi - 23 Jan 2006 16:06 GMT

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.

Signature

http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758

> Is it possible to get the full server name in the file path as opposed to
> just the drive letter that it refers to?
[quoted text clipped - 18 lines]
>
> Kim Mansfield

Reply to this Message

Tom Ogilvy - 23 Jan 2006 16:10 GMT

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

Signature

Regards,
Tom Ogilvy

> Is it possible to get the full server name in the file path as opposed to
> just the drive letter that it refers to?
[quoted text clipped - 18 lines]
>
> Kim Mansfield

Reply to this Message