How to Create a Computer Account from a Spreadsheet using VBScript

Guy recommends :
Free - WMI Monitor

---

Guy's Review of
Computer Utilities

1) FreePing
2) LanSurveyor
3) Xobni
4) PuTTY (UNIX)
5) WMI Monitor
6) BgInfo
7) Net-SNMP
8) Engineer's Toolset
9) DNS Stuff
10) WinDiff

---

Short computer jokes

Tutorial for Creating a Computer Account from a Spreadsheet

These example scripts build computer accounts in an Active Directory domain. What happens is that the script loops through the cells of a spreadsheet, the value in each cell will be the name of a new computer count.  Regard the scripts on this page as an extension of my basic create computer VBScript.

Topics for Creating Computer Account from a Spreadsheet

• Our Mission and Goals
• Example 1 - Create Computer Accounts from a Spreadsheet
• VBScript Tutorial - Learning Points
• Example 2 - Add error-correcting code
• Summary of creating computer accounts

 ♣

Our Mission and Goals

Our goal is to read information from a spreadsheet, and to use that data as a source of names for new computer accounts.  Whereas my previous script only created one computer account, this script is limited only by the number names that you list in the Excel spreadsheet.

The technical advance in this VBScript is to employ CreateObject("Excel.Application"), which gives us a handle on the spreadsheet.  After connecting to Excel, all we need is to script a, Do... Until empty loop.  This instruction cycles through the cells reading the name of each new computer.

Example 1: - Sample Script to Create Computer Accounts from a Spreadsheet 

Prerequisites

Recommended: that you complete my basic script as a refresher on how VBScript binds to Active directory.  If possible, logon as administrator, preferably at a domain controller.

Create a spreadsheet with your prospective computer names in the first column.  Be aware that where you save this .xls file should correspond to the strSheet variable in the script below.  For example: E: \scripts\Computers.xls.

Instructions for Creating Computer Accounts from a Spreadsheet

1. You need access to a Windows Active Directory domain.
2. Check the prerequisite to create a spreadsheet.
3. Copy and paste the example script below into notepad or a VBScript editor.
4. Decide whether to change the value for strSheet.
5. Create the names of the new computer accounts in the first column of the spreadsheet.
6. Save the file with a .vbs extension, for example: ComputerSpreadsheet .vbs.
7. Double click ComputerSpreadsheet .vbs and check the Computers container for strComputer.

Script to Create Computer Accounts from a Spreadsheet

VBScript Tutorial - Learning Points

Note 1:  See how CreateObject("Excel.Application") creates an instance of Excel.

Note 2:  Observe how we employ the Open method, just as if we clicked on the File menu: objExcel.Workbooks.Open(strSheet)

Note 3:  When I first ran this script I noticed zillions of instance of Excel in the Task Manger, this is how I cured that problem; I added objExcel.Quit.

Guy Recommends: SolarWinds LANSurveyor

LANSurveyor will produce a neat diagram of your network topology.  But that's just the start; LANSurveyor can create an inventory of the hardware and software of your machines and network devices.  Other neat features include dynamic update for when you add new devices to your network.  I also love the ability to export the diagrams to Microsoft Visio.

Finally, Guy bets that if you take a free trial of LANSurveyor then you will find a device on your network that you had forgotten about, or someone else installed without you realizing!

Download a Free Trial of LANSurveyor

Example 2 - Sample Script with Added error-correcting Code

Here is a bonus script which has the same goal, namely to create computer accounts, but this example anticipates problems.  For example, the computer name may already exist, you have already run the script.

VBScript Tutorial - Learning Points

Note 1:  This advanced version of Example 1 employs error-correcting code in the form of 'On Error Resume Next'.  In addition there is a basic error handling routing 'If err.number <> vbEmpty'  (is not null).

Note 2:  I incorporated an optional extra section, which launches the Active Directory Users and Computers snap-in.  My idea is twofold, to show that the script has completed, and also to point you where to check what has happened.

Summary of Creating Computer Accounts from a Spreadsheet

The ability to create Computer Accounts from spreadsheets really transforms VBScript.  In fact, this technique of reading data from files can be applied elsewhere.  From a scripting point of view, the simple but effect loop technique makes all the difference.  Like so many scripts, it mimics the actions you take to create computers manually, just imagine how you might copy names from a data file, then paste them into an Active Directory Users and Computer dialog box.

Guy Thomas recommends Computer Training Software. 

Their topics and material are ideal for getting you started with VBScript.  The videos are easy to follow and you can control the pace.  Try their free demo material and then see if you want to buy the full package. See more about VB Script Training CD.

See Also

● Create Computer        ● Create a user from a Spreadsheet