List all Network Adapter, MAC , Enable Disable Connection using VBA

List all Network Adapters using Excel VBA

Excel VBA code in this page can list all the network adapters in your computer along with its complete attributes.

Network adapters are used to connect a computer to a LAN (Local Area Network) or Internet. This can be done by wired (Ethernet) or a wireless connection. The network adapter is the connection point between computer & network.

One of the well known attribute of a network adapter is MAC address. ( i.e.,Media Access Control Address).

Including this, we can retrieve about 40 attributes of a network adapter from this below code.

VBA code to Get Network Adapter Name, MAC, Connection Status

Create a new workbook, open VB editor by pressng Alt + F11. Insert new module and copy paste the below code into the new module.

Then press F5. this code will fetch all the 40 attributes of each network adapters in your machine and list them in sheet 1.

Sub Get_All_Network_Adapter_Details()
    Dim iSh As Worksheet, i As Integer
    
    'Get All Network Adapter Details
    Set iSh = ThisWorkbook.Sheets(1)
    Set wmi = GetObject("winmgmts:root\CIMV2")
    Set adapters = wmi.ExecQuery("Select * from Win32_NetworkAdapter")

    'Attributes of Each Adapter
    iSh.Cells(1, 1) = "AdapterType"
    iSh.Cells(1, 2) = "AdapterTypeID"
    iSh.Cells(1, 3) = "AutoSense"
    iSh.Cells(1, 4) = "Availability"
    iSh.Cells(1, 5) = "Caption"
    iSh.Cells(1, 6) = "ConfigManagerErrorCode"
    iSh.Cells(1, 7) = "ConfigManagerUserConfig"
    iSh.Cells(1, 8) = "CreationClassName"
    iSh.Cells(1, 9) = "Description"
    iSh.Cells(1, 10) = "DeviceID"
    iSh.Cells(1, 11) = "ErrorCleared"
    iSh.Cells(1, 12) = "ErrorDescription"
    iSh.Cells(1, 13) = "GUID"
    iSh.Cells(1, 14) = "Index"
    iSh.Cells(1, 15) = "InstallDate"
    iSh.Cells(1, 16) = "Installed"
    iSh.Cells(1, 17) = "InterfaceIndex"
    iSh.Cells(1, 18) = "LastErrorCode"
    iSh.Cells(1, 19) = "MACAddress"
    iSh.Cells(1, 20) = "Manufacturer"
    iSh.Cells(1, 21) = "MaxNumberControlled"
    iSh.Cells(1, 22) = "MaxSpeed"
    iSh.Cells(1, 23) = "Name"
    iSh.Cells(1, 24) = "NetConnectionID"
    iSh.Cells(1, 25) = "NetConnectionStatus"
    iSh.Cells(1, 26) = "NetEnabled"
    iSh.Cells(1, 27) = "NetworkAddresses"
    iSh.Cells(1, 28) = "PermanentAddress"
    iSh.Cells(1, 29) = "PhysicalAdapter"
    iSh.Cells(1, 30) = "PNPDeviceID"
    iSh.Cells(1, 31) = "PowerManagementCapabilities"
    iSh.Cells(1, 32) = "PowerManagementSupported"
    iSh.Cells(1, 33) = "ProductName"
    iSh.Cells(1, 34) = "ServiceName"
    iSh.Cells(1, 35) = "Speed"
    iSh.Cells(1, 36) = "Status"
    iSh.Cells(1, 37) = "StatusInfo"
    iSh.Cells(1, 38) = "SystemCreationClassName"
    iSh.Cells(1, 39) = "SystemName"
    iSh.Cells(1, 40) = "TimeOfLastReset"

    'Get Details of Each Network Adapter Attribute
    i = 2
    For Each adapter In adapters
        With adapter
            iSh.Cells(i, 1) = .AdapterType
            iSh.Cells(i, 2) = .AdapterTypeID
            iSh.Cells(i, 3) = .AutoSense
            iSh.Cells(i, 4) = .Availability
            iSh.Cells(i, 5) = .Caption
            iSh.Cells(i, 6) = .ConfigManagerErrorCode
            iSh.Cells(i, 7) = .ConfigManagerUserConfig
            iSh.Cells(i, 8) = .CreationClassName
            iSh.Cells(i, 9) = .Description
            iSh.Cells(i, 10) = .DeviceID
            iSh.Cells(i, 11) = .ErrorCleared
            iSh.Cells(i, 12) = .ErrorDescription
            iSh.Cells(i, 13) = .GUID
            iSh.Cells(i, 14) = .Index
            iSh.Cells(i, 15) = .InstallDate
            iSh.Cells(i, 16) = .Installed
            iSh.Cells(i, 17) = .InterfaceIndex
            iSh.Cells(i, 18) = .LastErrorCode
            iSh.Cells(i, 19) = .MACAddress
            iSh.Cells(i, 20) = .Manufacturer
            iSh.Cells(i, 21) = .MaxNumberControlled
            iSh.Cells(i, 22) = .MaxSpeed
            iSh.Cells(i, 23) = .Name
            iSh.Cells(i, 24) = .NetConnectionID
            iSh.Cells(i, 25) = .NetConnectionStatus
            iSh.Cells(i, 26) = .NetEnabled
            iSh.Cells(i, 27) = .NetworkAddresses
            iSh.Cells(i, 28) = .PermanentAddress
            iSh.Cells(i, 29) = .PhysicalAdapter
            iSh.Cells(i, 30) = .PNPDeviceID
            iSh.Cells(i, 31) = .PowerManagementCapabilities
            iSh.Cells(i, 32) = .PowerManagementSupported
            iSh.Cells(i, 33) = .ProductName
            iSh.Cells(i, 34) = .ServiceName
            iSh.Cells(i, 35) = .Speed
            iSh.Cells(i, 36) = .Status
            iSh.Cells(i, 37) = .StatusInfo
            iSh.Cells(i, 38) = .SystemCreationClassName
            iSh.Cells(i, 39) = .SystemName
            iSh.Cells(i, 40) = .TimeOfLastReset

            'adapter.disable()
            'adapter.enable()
        End With
        i = i + 1
    Next
End Sub

Notice there are 2 commented lines towards the end of the code. These commented code can be used to enable or disable a network adapter in your machine.

What we are going to achieve here can also be displayed using a one line Command prompt command. open a cmd promt and try this command as well.

wmic nic get AdapterType, Name, MACAddress

For example: You can choose to disable/switch off wired connection and enable the Wireless connection alone.

Attribute list source: The list of all 40 attributes of network adapter is taken from this link.

All possible values for each attribute and their meaning can be found in this link.

Leave a Reply