Determining the Drive Type
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