Sign in Subscribe
4 min read

How to Export AD Computers to Excel and Separate Them by IP Address

How to Export AD Computers to Excel and Separate Them by IP Address
Photo by Sunrise King / Unsplash

At my job, I needed to find all the Windows 10 computers in our Active Directory and figure out which areas or locations they were assigned to. I also wanted to identify which systems were online or offline, spot any outdated operating systems, and have everything neatly organized for reporting.

While there are advanced tools that can do this, I wanted a simple, script-based approach that anyone could run without installing extra software. After building this workflow, I realized it was too helpful not to share — so here’s how you can do it too.

Step 1: Export AD Computer Information Using PowerShell

The first step is to export a list of computers from Active Directory that includes their Name, Operating System, and IP Address. Open PowerShell as Administrator and run this command:

PowerShell Command 
Get-ADComputer -Filter * -Property Name, OperatingSystem, IPv4Address |
Select-Object Name, OperatingSystem, IPv4Address |
Export-Csv -Path "C:\ADReport\ADComputersList.csv" -NoTypeInformation
Notes

• Change the file path (C:\ADReport\ADComputersList.csv) to a folder that exists on your computer.
• The resulting file will include Name, OperatingSystem, and IPv4Address.
• Once opened in Excel, filter the OperatingSystem column for “Windows 10” to quickly isolate those systems.

Step 2: Use Excel VBA to Separate Data by IP Address

Important: Excel Sheet Name Requirement

Before running the VBA script, make sure your worksheet is named Sheet1. If your data is on a sheet with a different name, either rename it to Sheet1 or update the line in the VBA script: Set ws = ThisWorkbook.Sheets("Sheet1") Replace “Sheet1” with the exact name of your data sheet, otherwise the script will fail.

Info: About IP Mapping and Areas

If your IP scheme maps to specific physical areas (for example, 10.10.1.x = HQ, 10.10.2.x = Warehouse), this step will automatically create separate worksheets for each unique IP address. This makes it easy to identify which computers are in each area, spot offline systems, or find outdated operating systems.

  1. In Excel, press ALT + F11 to open the VBA editor.
  2. Go to Insert → Module.
  3. Copy and paste the following VBA code into the new module:
Excel VBA Script 
Sub SeparateByIPPrefix()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim lastRow As Long
    Dim IPRange As Range
    Dim cell As Range
    Dim sheetName As String

    ' Set the worksheet with your data
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust "Sheet1" to the name of your main worksheet

    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Loop through each IP in the IP column
    Set IPRange = ws.Range("C2:C" & lastRow)
    For Each cell In IPRange
        ' Get the first three segments of the IP address as the sheet name
        sheetName = GetIPPrefix(cell.Value)
        
        ' Check if a worksheet with this name already exists
        If Not WorksheetExists(sheetName) Then
            ' Create a new worksheet for this IP prefix
            Set wsNew = ThisWorkbook.Sheets.Add
            wsNew.Name = sheetName

            ' Copy headers
            ws.Rows(1).Copy wsNew.Rows(1)
        Else
            Set wsNew = ThisWorkbook.Sheets(sheetName)
        End If

        ' Copy the row to the new worksheet
        ws.Rows(cell.Row).Copy wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next cell
End Sub

Function GetIPPrefix(ipAddress As String) As String
    Dim parts() As String

    ' Split the IP address by periods
    parts = Split(ipAddress, ".")

    ' Check if the IP address has at least three segments
    If UBound(parts) >= 2 Then
        ' Combine the first three segments with underscores
        GetIPPrefix = parts(0) & "_" & parts(1) & "_" & parts(2)
    Else
        ' If the IP format is unexpected, return a default value
        GetIPPrefix = "Invalid_IP"
    End If
End Function

Function WorksheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    WorksheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function
  1. Close the VBA editor.
  2. Back in Excel, press ALT + F8, select SeparateByIP, and click Run.

Final Thoughts

This PowerShell and Excel workflow provides a straightforward way to generate a clean inventory of your network. It helps you locate all Windows 10 systems, identify their areas by IP range, and quickly find machines that are offline or running outdated operating systems.

Published by:

Member discussion