VBScript to Read and Update an Excel Spreadsheet with Computer Description from Active Directory

This article explains how to use a VBScript application to read a single-column Excel spreadsheet containing a list of computers, check that list against Active Directory (AD), and then update the spreadsheet with the corresponding computer's AD Description field, if present.

In the event the computer does not exist in Active Directory, the Description field on the Excel spreadsheet will be updated with the text "NOT FOUND IN AD."

In the event the computer exists in AD, but the description field in AD is empty, the Description field in the Excel spreadsheet will be updated with the word "BLANK" next to the computer on the list.

Below is an example of what the initial spreadsheet would look like.

WARNING: DO NOT HAVE EXCEL OPEN – Not even for other spreadsheets during the script run!

In this scenario, the below primary constraints were tested:

1) Include 2 valid AD server names with valid descriptions in AD, one with name

2) Include 1 valid AD server name, with no description blank, one non-existent server

3) Include 1 invalid AD server name

The tested list includes the specific entries listed below:

· FileserverA (this would have a description in AD)

· FileserverB (no description in AD)

· Test (this would have a description in AD)

· BrZmN (this would be a non-existent server)

Below is a screen-shot of the initial (pre-script) servers.xlsx document:

Other considerations and further steps

Preferably, delete any other worksheet tabs, so that the only tab remaining is “Sheet1.” Alternatively, you can forego the deletion of other tabs, since this script deals with “Sheet1” only.

Your initial “servers.xlsx” document should contain only the left-most Column 1 populated with your server names and, per the existing script design; your script should be located in your “c:\scripts” folder. Alternatively, you may already have a number of server names with Description fields already filled in. This will not be an issue, since the script automatically will bypass any Excel server record that already contains a non-blank description beside it in Column 2. Therefore, it is acceptable for your initial spreadsheet already to contain data in Column 2.

Note: You may, if desired, customize the script to have different behavior if Column 2 contains data; i.e., you may wish to have the script always update the Column 2 (Description) field in Excel with the then-current data found in AD (or with “BLANK” and/or “NOT FOUND IN AD" for each such occurrence).

After you ensure that you have entered the server names into your spreadsheet Column 1 as desired and required; save the following script to your “c:\scripts” with the file name “checkservers.vbs.” (Note: The assumption here is that you know how to open Notepad and paste and save the below code).

NOTE: Below is only a snippet (portion) of the full code for general understanding. The below code section WILL NOT WORK, unless you click and download/save the code from the embedded links below or from the "References" section!

The basic premise that the code uses is as follows:
1) Read through all rows of Col 1 on an Excel document (Main code section)

2) Read through each AD computer record (Subroutine section)

3) Update Excel with Description from AD (Main code section)

[Start of Code snippet section]

'Download the Full script for complete working code

'-------------------------------------------------------------------------

' Start of MAIN code (checkservers.vbs)

'-------------------------------------------------------------------------

' VBScript: checkservers.vbs

' Author: Jeff Mason aka TNJMAN aka bitdoctor

' 09/06/2013

'Basic premise: 1) Read through all rows of Col 1 on an Excel document

' 2) Read through each AD computer record

' 3) Update Excel with Description from AD

'Assumptions/Notes:

' 1) Create Excel document (c:\scripts\servers.xlsx) with ONE worksheet,

' containing only "server name" in Column 1

‘Other assumptions in the FULL SCRIPT, download now

' Assumptions:

' You must Set excelPath = "C:\scripts\servers.xlsx" (or wherever your xlsx file is)

' You must have at least "read" permissions to AD/LDAP

Option Explicit

Dim objExcel

Dim excelPath

Dim worksheetCount

Dim counter ' To count rows and/or columns

Dim currentWorkSheet

‘…

excelPath = "C:\scripts\servers.xlsx"

‘Full code is listed in the FULL SCRIPT, download now

WScript.Echo "Reading Data from Path/File: " & excelPath

Set objExcel = CreateObject("Excel.Application")

objExcel.DisplayAlerts = 0 ' Don't display any messages about conversion and so forth

WScript.Echo "-------------------------------------------------------"

WScript.Echo "Reading data from worksheet " & workSheetCount

WScript.Echo "-------------------------------------------------------" & vbCRLF

Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(workSheetCount)

' What is the leftmost column in the spreadsheet that has data in it

left = currentWorksheet.UsedRange.Column

Set Cells = currentWorksheet.Cells

'-----------------------------------------------------------------------------

' Row Loop - Loop through each row in the worksheet (but only for Column 1)

' Only deal with Cols 1 & 2 of Sheet1, since SERVER=Col1 and DESCRIPTION=Col2

' Column 2 is built by "checksvr" subroutine, based on Column 1)

For row = 0 to (usedRowsCount-1)

' only look at rows/cols in the "used" range

curRow = row+top

' curCol = column+left

If IsEmpty(strDescription) Then ' If Col 2 already populated, skip to next row in sheet

If Not (IsEmpty(server)) Then

‘Full code is listed in the FULL SCRIPT, download now

End If

' End Row loop

'-----------------------------------------------------------------------------

' Done with the current worksheet, release the memory

Set currentWorkSheet = Nothing

‘Save and close the workbook - Full code is listed in the FULL SCRIPT, download now

WScript.Echo "Finished."

Set currentWorkSheet = Nothing

' Finished with Excel object, release it from memory & get out !!!

Set objExcel = Nothing

WScript.Quit(0)

'-------------------------------------------------------------------------

' End of MAIN code

'-------------------------------------------------------------------------

' Subroutine (checksvr) to check for the sever name in Active Directory

'-------------------------------------------------------------------------

Sub checksvr(svr)

On Error Resume Next

' Point to the domain/ldap root

Set objRootDSE = GetObject("LDAP://RootDSE")

' Query all Active Directory (normally, leave this commented, query specific OU(s)

' strRoot = objRootDSE.Get("DefaultNamingContext") 'Uncomment to search ENTIRE AD TREE

' Query a specific Organizational Unit

strRoot = "OU=Servers,DC=YOUR-DOMAIN,DC=com" ' Comment this out, if searching ALL OF AD

‘…

objCn.Provider = "ADsDSOObject"

objCn.Open "Active Directory Provider"

' Filter the query for only sAMAccountName,description of any computers in AD

objCmd.commandtext = …

‘…

svrcmp = UCase(svr) & "$" 'Upper-case the Server entry from the spreadsheet for consistent compare

svrflag = "" 'Clear out the "found-server" flag

Do While Not objRes.EOF

' If description is blank/null, set the value to the word "BLANK"

strDescription = ""

If Not (IsNUll(objRes.Fields("description").Value)) Then

‘ …

‘Full code is listed in the FULL SCRIPT, download now

' We want to check ALL descriptions, including null descriptions

' But only for the server passed into this script as an argument

If svrcmp = objRes.Fields("sAMAccountName").Value Then

'If Excel server name found in AD, set svrflag = "TRUE" & end the subroutine

svrflag = "TRUE"

'Write this to the Excel spreadsheet / exit the subroutine

Exit Sub

End If

'Move to / read the next AD resource record

objRes.MoveNext

Loop

'If flag never set to "TRUE" then fall out through here - server not found in AD

strDescription = "NOT FOUND IN AD"

objRes.close

ObjCn.close

'-------------------------------------------------------------------------

End Sub

'-------------------------------------------------------------------------

[End of Code snippet section]

You must edit the full code and customize the “strRoot” variable in the script to match your own AD environment. Caution: Take care to modify only the 2^nd “strRoot” line, since the 1^st strRoot line is commented out. In the script, a generic line is included (strRoot = "OU=Servers,DC=YOUR-DOMAIN,DC=com"); this is the only line that should need to be customized, before saving your script.

As an example, if your domain is “contoso.com,” and your servers are located in the “Servers” Organizational Unit (OU), then the requisite modified “strRoot” line would look like the following:

strRoot = "OU=Servers,DC=contoso,DC=com"

After modifying the “strRoot” line to match your AD environment, save the modified script as “c:\scripts\checkservers.vbs.”

Next, invoke a command shell from your Windows workstation computer: Click “Start,“ then type “cmd” and press Enter. This will invoke the Windows Command Shell (often called the DOS Command Prompt).

Change your working directory to your scripts folder and execute the “checkservers.vbs” script (i.e., type “cscript checkservers.vbs” and press Enter):

Following is the output from a live run of the “checkservers.vbs” script, followed by the spreadsheet after the updates applied by the script:

c:\scripts>cscript checkservers.vbs

Microsoft (R) Windows Script Host Version 5.8

Reading Data from Path/File: C:\scripts\servers.xlsx

-------------------------------------------------------

Reading data from worksheet 1

-------------------------------------------------------

Finished.

c:\scripts>

After executing the “checkservers.vbs” script against the “servers.xlsx” initial spreadsheet, below is a screen-shot of the resultant, updated “servers.xlsx” spreadsheet:

Note: The script found a description for the first computer name, “FileserverA,” in AD and updated line 1 with the description, “Main file server.” The script found that “FileserverB” existed in AD, but had an empty Description field, thus the script updated the spreadsheet with the word “BLANK.” The script bypassed checking the computer named “Test,” because the spreadsheet already contained a description entry for that computer. The script did not find an entry for computer “BrZmN,” thus the script updated the spreadsheet with the phrase, “NOT FOUND IN AD.”