HELP Please VBA Hard Drive information
Hello,
I hope someone can help me. I've been trying desperately to find a macro which is checking all hard drives and showing me if is a hdd or ssd.
I used the macro below but it doesn't give me the right information. I also tried different Win32_ Codes but I couldn't make it work or I didn't get the right information. I tried following codes
WIN32_Physical media - shows me only one row even when it is supposed to show me more information such as mediatype. (https://docs.microsoft.com/en-us/pre...-physicalmedia)
WIN32_DriveDisk - no infos about hard drive type
and many more from here = https://docs.microsoft.com/en-us/win...torage-classes
Thanks in advance.
Cheers Felix
Sub LogicalDiskWMI()
sWQL = "Select * From Win32_LogicalDisk"
Set oWMISrvEx = GetObject("winmgmts:root/CIMV2")
Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)
intRow = 2
strRow = Str(intRow)
ThisWorkbook.Sheets("LogicalDisk").Range("A1").Value = "Name"
ThisWorkbook.Sheets("LogicalDisk").Cells(1, 1).Font.Bold = True
ThisWorkbook.Sheets("LogicalDisk").Range("B1").Value = "Value"
ThisWorkbook.Sheets("LogicalDisk").Cells(1, 2).Font.Bold = True
For Each oWMIObjEx In oWMIObjSet
For Each oWMIProp In oWMIObjEx.Properties_
If Not IsNull(oWMIProp.Value) Then
If IsArray(oWMIProp.Value) Then
For n = LBound(oWMIProp.Value) To UBound(oWMIProp.Value)
Debug.Print oWMIProp.Name & "(" & n & ")", oWMIProp.Value(n)
ThisWorkbook.Sheets("LogicalDisk").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).Value = oWMIProp.Value(n)
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft
intRow = intRow + 1
strRow = Str(intRow)
Next
Else
ThisWorkbook.Sheets("LogicalDisk").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).Value = oWMIProp.Value
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft
intRow = intRow + 1
strRow = Str(intRow)
End If
End If
Next
'End If
Next
End Sub
