List and Type of Drives (VBA)

Vishesh's picture
Copy the following code in any general module and run. This will display the list of all drives available with their types.
Sub DriveTypeAndList()
    Dim objDrv      As Object
    Dim strMsg      As String
 
    For Each objDrv In CreateObject("Scripting.FileSystemObject").Drives
        Select Case objDrv.DriveType
            Case 0: strMsg = strMsg & vbNewLine & objDrv.DriveLetter & ": Unknown"
            Case 1: strMsg = strMsg & vbNewLine & objDrv.DriveLetter & ": Removable Drive"
            Case 2: strMsg = strMsg & vbNewLine & objDrv.DriveLetter & ": Hard Disk Drive"
            Case 3: strMsg = strMsg & vbNewLine & objDrv.DriveLetter & ": Network Drive"
            Case 4: strMsg = strMsg & vbNewLine & objDrv.DriveLetter & ": CDROM Drive"
            Case 5: strMsg = strMsg & vbNewLine & objDrv.DriveLetter & ": RAM Disk Drive"
        End Select
    Next
 
    Set objDrv = Nothing
    MsgBox strMsg, vbInformation, "Excel Experts Tip"
End Sub
YasserKhalil's picture

Great code Thanks for sharing

Great code
Thanks for sharing it
How can I get results in column A?