Retrieve USB Device Information (VBA & WMI)

Introduction

The code that you will find below was written due to 2 main reasons: a problem that I faced while I was trying to find the manufacturer of a USB device (a typical USB stick), as well as a discussion on a Linkedin group regarding the “power of WMI” on various windows tasks. The discussion was held before getting into trouble with the USB stick, so when the problem came up, I knew where I should search for a solution.

The problem was not something serious. A friend of mine has an old 2 GB USB stick. He was using this stick for many years to transfer data between his work computer and his home computer. Through the years, the logo of the manufacturer was faded out (he used the USB stick as a key ring), so, when recently he decided to buy a new one, he couldn’t find the name of the USB manufacturer. Since he was satisfied with the old USB stick, he wanted to buy a new one from the same manufacturer.

My first thought was to plug in the stick and go on the device manager, believing that the manufacturer’s name would be written somewhere there (i.e. on my hard disk at the device manager writes “ST1000LM024” – a Seagate 1TB hard disk). Unfortunately, in the case of the USB stick, I was wrong! I know that this seems strange, but it happened! Then, the discussion on the Linkedin group came to my mind. I developed the VBA code, I plugged in the USB stick on my computer, I run the code and the “mystery” was finally solved. The manufacturer of the USB stick was Kingston! Although the issue was trivial, the WMI was here once again to the rescue.

VBA code to retrieve USB device information 

The code is based on a WMI script. The Win32_USBControllerDevice and the Win32_PnPEntity classes are used to retrieve the necessary data. The Win32_USBControllerDevice association WMI class relates a universal serial bus (USB) controller and the CIM_LogicalDevice instance connected to it. The Win32_PnPEntity WMI class represents the properties of a Plug and Play device. Plug and Play entities are shown as entries in the Device Manager located in the Control Panel of Windows.

Option Explicit

Sub RetrieveUSBInfo()

    '--------------------------------------------------------------------------------------------------
    'Loops through all the USB controllers and devices (sticks, hubs, etc.) and retrieves information.
    'The code uses a WMI script in order to access the Win32_USBControllerDevice class.
           
    'Written By:    Christos Samaras
    'Date:          13/01/2014
    'E-mail:        [email protected]
    'Site:          https://myengineeringworld.net
    '--------------------------------------------------------------------------------------------------
   
    'Declaring the necessary variables.
    Dim strComputer     As String
    Dim strDeviceName   As String
    Dim objWMIService   As Object
    Dim colControllers  As Object
    Dim objController   As Object
    Dim colUSBDevices   As Object
    Dim objUSBDevice    As Object
    Dim i               As Integer
    
    'Just in case of an error...
    On Error Resume Next
    
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Clear the sheet (except headings).
    shUSB.Range("A2:E1048576").ClearContents
    
    'Set the computer.
    strComputer = "."
    
    'The root\cimv2 namespace is used to access the Win32_USBControllerDevice class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    
    'A select query is used to get the list of all USB controllers.
    Set colControllers = objWMIService.ExecQuery("Select * From Win32_USBControllerDevice")
    
    'Start below sheet headings.
    i = 2
    
    'Loop through all the collection of USB controllers.
    For Each objController In colControllers
       
       'Retrieve the device name from the controller.
       strDeviceName = Replace(objController.Dependent, Chr(34), "")
       strDeviceName = Right(strDeviceName, Len(strDeviceName) - WorksheetFunction.Find("=", strDeviceName))
       
       'Execute a select query on Win32_PnPEntity class based on device name.
       Set colUSBDevices = objWMIService.ExecQuery("Select * From Win32_PnPEntity Where DeviceID = '" & strDeviceName & "'")
       
       'Loop through all the USB devices and write the necessary data in the sheet.
       For Each objUSBDevice In colUSBDevices
            With shUSB
                .Cells(i, 1).Value = objUSBDevice.Name
                .Cells(i, 2).Value = objUSBDevice.Manufacturer
                .Cells(i, 3).Value = objUSBDevice.Status
                .Cells(i, 4).Value = objUSBDevice.Service
                .Cells(i, 5).Value = objUSBDevice.DeviceID
            End With
            i = i + 1
        Next
    Next
    
    'Adjust the columns' width.
    shUSB.Columns("A:E").AutoFit
    
    'Inform the user about the process.
    MsgBox "Information from " & i - 2 & " USB devices was retrieved successfully!", vbInformation, "Finished"
    
End Sub 

Downloads

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

Read also

Excel Macro To List All Computer Software
Register & Unregister A DLL File Through VBA
Save Web Pages As PDF Files (check the CheckPrinterStatus function)