Computer Performance, VBScript

How to Create a Computer Account from a Spreadsheet using VBScript

Tutorial for Creating a Computer Account from a SpreadsheetVBScript to create 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

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

' ComputerSpreadsheet .vbs
' Sample VBScript to Create Computer Accounts from a Spreadsheet
' Author Guy Thomas http://computerperformance.co.uk/
' Version 1.2 - May 2010
' ------------------------------------------------------'
Option Explicit
Dim strComputer, strOU, strSheet, intRow
Dim objRootLDAP, objContainer, objComputer, objShell
Dim objExcel, objSpread

' -----------------------------------------------'
' Important change OU= and strSheet to reflect your domain
' -----------------------------------------------'

strOU = "OU=Accounts7 ," ' Note the comma
strSheet = "E:\scripts\Computers1.xls"

' Bind to Active Directory, Computers container.
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & _
objRootLDAP.Get("defaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSpread = objExcel.Workbooks.Open(strSheet)
intRow = 2 'Row 1 often containes headings

' Here is the loop that cycles through the cells
Do Until objExcel.Cells(intRow,1).Value = ""
   strComputer = objExcel.Cells(intRow, 1).Value

   ' Build the actual computer.
   Set objComputer = objContainer.Create("Computer", _
   "cn=" &   strComputer)
   objComputer.Put "sAMAccountName", strComputer & "$"
   objComputer.Put "userAccountControl", 4096
   objComputer.SetInfo
   intRow = intRow + 1
Loop
objExcel.Quit

WScript.Quit

' End of Sample ComputerSpreadsheet  VBScript.

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' Free Bulk Import ToolFree Download Solarwinds Bulk Import Tool

Import users from a spreadsheet.  Just provide a list of the users with their fields in the top row, and save as .csv file.  Then launch this FREE utility and match your fields with AD's attributes, click and import the users.

Optionally, you can provide the name of the OU where the new accounts will be born. Download your FREE bulk import tool.

If you need more comprehensive software, download a free trial of SAM (Server & Application Monitor)

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.

 

' ComputerSpreadsheetADV.vbs
' Sample VBScript to Create Computer Accounts from a Spreadsheet
' Author Guy Thomas http://computerperformance.co.uk/
' Version 1.4 - May 2010
' ------------------------------------------------------'
Option Explicit
Dim strComputer, strOU, strSheet, intRow
Dim objRootLDAP, objContainer, objComputer, objShell
Dim objExcel, objSpread

' -----------------------------------------------'
' Important change OU= and strSheet to reflect your domain
' -----------------------------------------------'

strOU = "OU=Accounts7 ," ' Note the comma
strSheet = "E:\scripts\Computers1.xls"

' Bind to Active Directory, Computers container.
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & _
objRootLDAP.Get("defaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSpread = objExcel.Workbooks.Open(strSheet)
intRow = 2 'Row 1 often contains headings

' Here is the loop to cycle through the cells
Do Until objExcel.Cells(intRow,1).Value = ""
   strComputer = objExcel.Cells(intRow, 1).Value

   ' Build the actual computer and add error-correcting code
   On Error Resume next
   Set objComputer = objContainer.Create("Computer", _
   "cn=" & strComputer)
   objComputer.Put "sAMAccountName", strComputer & "$"
   objComputer.Put "userAccountControl", 4096
   objComputer.SetInfo
   If err.number <> vbEmpty Then
      Wscript.Echo "Error " & err.number
      End If
   intRow = intRow + 1
Loop
objExcel.Quit

' Optional section to launch Active Directory Uses and Computers
Set objShell=CreateObject("WScript.Shell")
objShell.Run "%systemroot%\system32\dsa.msc"
WScript.Quit

' End of Sample ComputerSpreadsheetADV VBScript.

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.

If you like this page then please share it with your friends

 


See more VBScript examples:

VBScript create users   • VBScript create contact   • Create contact Exchange   • VBS PwdLastSet

VBScript create computer   • PowerShell create computer from spreadsheet   • Free Import Users Tool

VBScript change password  • VBScript to create group   • SolarWinds Free WMI Monitor

 *


Custom Search

Site Home

Guy Recommends: WMI Monitor for PowershellSolarwinds WMI Monitor

Windows Management Instrumentation (WMI) is most useful for PowerShell scripting.

SolarWinds have produced this Free WMI Monitor to take the guess work out of which WMI counters to use for applications like Microsoft Active Directory, SQL or Exchange Server.

Download your free copy of WMI Monitor

Author: Guy Thomas Copyright © 1999-2017 Computer Performance LTD All rights reserved.

Please report a broken link, or an error to: