Determining The Drive Type
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.
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks