Determining The Drive Type

Category: VBA Functions | [Item URL]

A companion file is available: Click here to download

This tip contains a VBA function that uses the Windows GetDriveType API function to determine the type of a particular drive.

Private Declare Function GetDriveType Lib "kernel32" _
  Alias "GetDriveTypeA" (ByVal nDrive As String) As Long

Function DriveType(DriveLetter As String) As String
'  Returns a string that describes the type of drive of DriveLetter
   DriveLetter = Left(DriveLetter, 1) & ":\"
   Select Case GetDriveType(DriveLetter)
      Case 0: DriveType = "Unknown"
      Case 1: DriveType = "Non-existent"
      Case 2: DriveType = "Removable drive"
      Case 3: DriveType = "Fixed drive"
      Case 4: DriveType = "Network drive"
      Case 5: DriveType = "CD-ROM drive"
      Case 6: DriveType = "RAM disk"
      Case Else: DriveType = "Unknown drive type"
  End Select
End Function

The function accepts a drive letter, and returns a string that describes the type of drive. Network drives must be mapped to a single-letter drive designator.

Example

The example below lists all drives, and their type. The information is sent to columns A and B of the active worksheet.

Sub ShowAllDrives()
    Dim LetterCode As Long
    Dim Row As Long
    Dim DT As String
    Row = 1
    For LetterCode = 65 To 90 ' A-Z
        DT = DriveType(Chr(LetterCode))
        If DT <> "Non-existent" Then
            Cells(Row, 1) = Chr(LetterCode) & ":\"
            Cells(Row, 2) = DT
            Row = Row + 1
        End If
    Next LetterCode
End Sub

The downloadable file also contains functions that return the total drive size, and space available.