Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading a drive 'name' (Excel 2000)

    Staff have their own personal area as the 'H:' drive, and in the File/Open dialog etc. it displays their name as well as the drive letter. In my Excel code is there a way that I can read this information? Or does it require using an API? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reading a drive 'name' (Excel 2000)

    Yes, you need an API functtion. See (for example) the thread starting at <post#=188162>post 188162</post#>.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Reading a drive 'name' (Excel 2000)

    The API is cleaner, but I think the Scripting.FileSystemObject can retrieve the ServerShare information, too.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Reading a drive 'name' (Excel 2000)

    If using FileSystemObject, you would use the Drive object's ShareName property to get this information. Example:

    <code>Public Function GetShareName(ByRef sDriveSpec As String) As String</code>
    <code></code>
    <code> Dim fso As New FileSystemObject</code>
    <code> Dim d As Scripting.Drive</code>
    <code> </code>
    <code> ' GetDrive - drivespec arg can be drive letter ("C"), _</code>
    <code> drive letter w/colon ("C:") or w/path separator ("C:") _</code>
    <code> or can be network share spec ("Server1Share1")</code>
    <code> </code>
    <code> ' If not a network drive, ShareName property returns a zero-length string</code>
    <code> </code>
    <code> If fso.DriveExists(sDriveSpec) Then</code>
    <code> Set d = fso.GetDrive(sDriveSpec)</code>
    <code> GetShareName = d.ShareName</code>
    <code> Else</code>
    <code> GetShareName = vbNullString</code>
    <code> End If</code>
    <code> </code>
    <code> Set fso = Nothing</code>
    <code> Set d = Nothing</code>
    <code></code>
    <code>End Function</code>

    Note that you'd probably want to test that drive spec is valid, as shown, or else error will result. Actual function should include error handler. Requires reference to Scripting Runtime library (or use CreateObject - modify code as follows):

    Dim fso As Object
    Dim d As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    (rest of function same as above)

    HTH

Posting Permissions

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