+ Reply to Thread
Results 1 to 5 of 5

Server name in Excel file path

  1. #1
    John Mansfield
    Guest

    Server name in Excel file path

    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


  2. #2
    Bob Phillips
    Guest

    Re: Server name in Excel file path

    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)

    "John Mansfield" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    galimi
    Guest

    RE: Server name in Excel file path

    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.
    --
    http://HelpExcel.com
    1-888-INGENIO
    1-888-464-3646
    x0197758


    "John Mansfield" wrote:

    > 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
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: Server name in Excel file path

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


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

    --
    Regards,
    Tom Ogilvy

    "John Mansfield" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  5. #5
    John Mansfield
    Guest

    Re: Server name in Excel file path

    Thanks Bob. I appreciate your help.


    "Bob Phillips" wrote:

    > 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)
    >
    > "John Mansfield" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1