Read The USB Drive Serial Number (VBA & WMI Solution)

Share this

February 24, 2019

Read The USB Drive Serial Number


Introduction


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’).

Serial Numbers From All The Drives

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 

 


Downloads


Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

 


Read also


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

Page last modified: February 20, 2020

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>