I recently received an email from Bahman asking me if it is possible to get the serial number of a USB drive using Excel and VBA. The answer is yes, although the code is based on Windows Management Instrumentation (WMI), the infrastructure for management data and operations on Windows-based operating systems.
In the past, we have seen several code examples on how to write WMI-related scripts in VBA, such as for setting the default Windows printer, getting battery information, registering a COM DLL, retrieving a MAC address, and many others. The interesting part of this case study was the WMI class that had to be used. One could be tempted to use the Win32_USBController class, which manages the capabilities of a universal serial bus (USB) controller. Although this class seems quite relevant, it does not expose any serial number property, so a different class is required.
VBA code for reading the serial number from a USB drive
The code is based on the Win32_DiskDrive WMI class, which represents a physical disk drive as seen by a computer running the Windows operating system. When querying for all the objects that belong to the particular class, a WHERE clause is used to restrict the search on USB drives only (WHERE InterfaceType=’USB’).
If the WHERE clause is omitted, the information from all the drive objects will be retrieved (e.g. IDE drives). Check the above image that shows the results from my computer (3 drives, one of which is a USB drive) when omitting the WHERE clause in the VBA code.
Option Explicit Sub RetrieveUSBDriveSerialNumber() '------------------------------------------------------------------------- 'Loops through all the computer drives and retrieves useful information. 'The code uses a WMI script in order to access the Win32_DiskDrive class. 'Written By: Christos Samaras 'Date: 23/02/2019 'E-mail: [email protected] 'Site: https://www.myengineeringworld.net '------------------------------------------------------------------------- 'Declaring the necessary variables. Dim strComputer As String Dim objWMIService As Object Dim colDrives As Object Dim objDrive As Object Dim i As Integer 'In case of error... On Error Resume Next 'Disable screen flickering. Application.ScreenUpdating = False 'Clear the sheet contents. ThisWorkbook.Sheets("USB Serial Number").Range("B4:F23").ClearContents 'Set the computer. strComputer = "." 'The root\cimv2 namespace is used to access the Win32_DiskDrive class. Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 'A select query is used to get the list of all the USB drives. Set colDrives = objWMIService.ExecQuery("SELECT * FROM Win32_DiskDrive WHERE InterfaceType='USB'") 'If you need to get information from all the drives, use this query. 'Set colDrives = objWMIService.ExecQuery("SELECT * FROM Win32_DiskDrive") 'If an error occurs in the previous step, the macro should stop. If Err.Number <> 0 Then 'Re-enable the screen. Application.ScreenUpdating = True 'Inform the user about the error. MsgBox "When attempting to get the drive information the following error occurred:" & vbNewLine & _ Err.Description, vbCritical, "Error " & Err.Number Exit Sub End If 'Start just below the sheet headings. i = 4 'Loop through all the USB drives and write the necessary data into the sheet. For Each objDrive In colDrives With ThisWorkbook.Sheets("USB Serial Number") .Cells(i, 2).Value = objDrive.InterfaceType .Cells(i, 3).Value = objDrive.Model .Cells(i, 4).Value = objDrive.Name .Cells(i, 5).Value = objDrive.Size / (1024 ^ 3) 'Convert the size to GB. .Cells(i, 6).Value = Trim(objDrive.SerialNumber) 'Remove some irrelevant spaces. End With i = i + 1 Next 'Adjust columns width. ThisWorkbook.Sheets("USB Serial Number").Columns("B:F").AutoFit 'Release the objects. Set objDrive = Nothing Set colDrives = Nothing Set objWMIService = Nothing 'Re-enable the screen. Application.ScreenUpdating = True 'Inform the user about the process. If i - 4 = 1 Then MsgBox "Information from a single USB drive was successfully retrieved!", vbInformation, "Finished" Else MsgBox "Information from " & i - 4 & " USB drives was successfully retrieved!", vbInformation, "Finished" End If End Sub
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Retrieve USB Device Information (VBA & WMI)
Get & Set The Default Windows Printer With VBA
Get Laptop’s Battery Information Through VBA
Get Public IP, Local IP & MAC Address Using VBA
Register & Unregister A DLL File Through VBA
Excel Macro To List All Computer Software