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

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]
' 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
' 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 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
' 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
   'If flag never set to "TRUE" then fall out through here - server not found in AD
    strDescription = "NOT FOUND IN AD"
 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 2nd “strRoot” line, since the 1st 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
Copyright (C) Microsoft Corporation. All rights reserved.
Reading Data from Path/File: C:\scripts\servers.xlsx
Reading data from worksheet 1

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.”


See Also

The above-referenced TechNet published script can be obtained from above embedded links or from the following link:
Reason for creating the script - I saw the below “Script Request” posted 09/02/2013 on TechNet, and my script is the solution:
Base script to read an Excel spreadsheet was found here:
Mr. Greg Hatcher’s script was the base code for reading an Excel sheet:
Base code for parsing through AD was found here:
Mr. Gregory Shiro’s script for parsing AD was found in TechNet forums (used for subroutine):

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
  • Maheshkumar S Tiwari edited Revision 22. Comment: Formatting and added tag

  • TNJMAN edited Revision 20. Comment: see also in Title case; and proper-cased Title itself.

  • TNJMAN edited Revision 19. Comment: See also

  • Maheshkumar S Tiwari edited Revision 18. Comment: Added tags

  • Maheshkumar S Tiwari edited Revision 17. Comment: Added Tag and title casing. Hi TNJMAN , you can go through Naomi's article on editing: social.technet.microsoft.com/.../19537.wiki-technical-editing.aspx

  • TNJMAN edited Revision 16. Comment: made it "more prettier" - LOL

  • TNJMAN edited Revision 15. Comment: final touches - updates welcomed!

  • TNJMAN edited Revision 14. Comment: better quality on some of the images

  • TNJMAN edited Revision 13. Comment: Explain the premise, clean up formatting & include embedded code snippet

  • TNJMAN edited Revision 10. Comment: Okay, I'm moderately satisfied with the format, but the script could be better formatted I guess

Page 1 of 2 (16 items) 12
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
  • TNJMAN edited Original. Comment: format? how do we properly format these?

  • TNJMAN edited Revision 1. Comment: format. LOL

  • Why do you have so many = inside the article? Please remove all extra =

  • TNJMAN edited Revision 3. Comment: try it differently - images later.