I'm using the following script to export user data from AD to Excel. I want to use the same spreadsheet to import changes I've made in that same spreadsheet to AD. I would also like the following script to search through sub OU's aswell. Can someone please help me?
'==========================================================================
'
' VBScript Source File -- Created with SAPIEN Technologies PrimalScript 3.0
'
' NAME: Dumps Users from Active Directory to Excel spreadsheet
'
' AUTHOR: xx
' DATE : 06.02.2004
'
' COMMENT:
' Dumps (copies) Users from AD to a Excel spreadsheet.
' This script Dumps users from the Windows NT DS via ADSI. The script writes user
' information to a EXCEL spreadsheet.
'
'The sample uses the directory root "
ldap://DC=win5,DC=local/"
'Change the directory path in the EXCEL spreadsheet (under DS Root) to match your DS
'before running this script. For Intility 1.x use this doesn't need to be changed.
'
'To Dump users, run DumpCustUsers.VBS with %windir%\"Your Samples Directory Here"\Users.XLS.
' e.g. from command line:
' "dumpcustusers.vbs C:\example\users.xls"
'
'==========================================================================
Const ADS_SCOPE_SUBTREE = 2
Dim oXL,oSheet
Dim u, s, ProxyAddressesList,aProxy, intCount
Dim c
Dim root
Dim ou
Dim TextXL
Dim CRLF
dim oArgs
Dim MyVar
Dim Count, ouCount, LineCount
Dim oUser, oContainer
Dim mob
Dim givenName_and_sn
Dim givenName_, sn_, mail_,sAMAccountName_,telephoneNumber_,company_
Dim address_,pobox_,postalCode_,stateProvince_,City_,mailalias_,description_
Dim title_,department_,country_,HomePhone_,pager_,fax_
'Get the command line args
Set oArgs=wscript.arguments
ouCount=0
Count=0
MyVar=1
root="DC=xxx,DC=local"
CRLF = Chr(13) & Chr(10)
'Start EXCEL and display it to the user
Set oXL = WScript.CreateObject("EXCEL.application")
oXL.Visible = True
'Open the workbook passed in the command line
'oXL.workbooks.open TextXL
oXL.Workbooks.Add
'Activate the Add page
'oXL.sheets("Dump").New
Set oSheet = oXL.ActiveWorkbook.Worksheets(1)
oSheet.Name = "UserInfo"
'Adjust column width
oXL.Columns(1).ColumnWidth=39
oXL.Columns.Range("B..C").ColumnWidth=20
oXL.Columns(4).ColumnWidth=50
oXL.Columns(5).ColumnWidth=16 'SAM Account
oXL.Columns.Range("F..G").ColumnWidth=18 'Work phone - Mobile
oXL.Columns(8).ColumnWidth=25 'Company
oXL.Columns(9).ColumnWidth=30 'Address
oXL.Columns(10).ColumnWidth=20 'P.O. Box
oXL.Columns(11).ColumnWidth=12 'Postal Code
oXL.Columns.Range("L..M").ColumnWidth=20
oXL.Columns.Range("N..O").ColumnWidth=50
oXL.Columns(16).ColumnWidth=15 ' Title
oXL.Columns(17).ColumnWidth=25 'Department
oXL.Columns(18).ColumnWidth=8 'Country
oXL.Columns.Range("S..U").ColumnWidth=18
oXL.Columns(22).ColumnWidth=30 'CN
oXL.Columns(23).ColumnWidth=40 'msExchUseOAB
oXL.Columns(24).ColumnWidth=40 'msExchQueryBaseDN
' Centre/justify headings
oXL.Range("A1:U2").Select
oXL.Selection.HorizontalAlignment = 3 ' xlCentre
' Left headings
oXL.Range("A3:U350").Select
oXL.Selection.HorizontalAlignment = 2 ' xlLeft
'oXL.Range("A3:U250").Select
oXL.Range("A3:U350").NumberFormat = "@" 'Sets selection to text format
'oXL.Selection.CellFormat.NumberFormat = "Text"
'Worksheets("Sheet1").Range("A17").NumberFormat = "General"
With oSheet.Range("A1..Z2").Font
.Name = "Arial"
.Size = 10
.bold = True
.underline = True
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = xlAutomatic
End With
'Some formatting
oSheet.Range("A2").Font.underline=False
oSheet.Range("A2").Font.bold=False
oSheet.Range("B1:C2").Font.ColorIndex=5 'Blue
oSheet.Range("D1:D2").Font.ColorIndex=10 'Green
oSheet.Range("E1:E2").Font.ColorIndex=3 'Red
oSheet.Range("N1:N2").Font.ColorIndex=10 'Green
oSheet.Range("V3:V350").Font.ColorIndex=15 'Grey
oSheet.Range("W3:W350").Font.ColorIndex=12
oSheet.Range("X3:X350").Font.ColorIndex=13
'ou=dummy,ou=testkunde,ou=customers
With oSheet.Range("A3..U250").Font
.Name = "Arial"
.Size = 10
.bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = xlAutomatic
End With
'Some more formatting
oSheet.Range("A3:A350").Font.ColorIndex=15 'Grey
'build sheet structure
oXL.ActiveSheet.range("A1").Activate
oXL.activecell.Value="DS Root"
oXL.activecell.offset(0, 1).Value="First Name"
oXL.activecell.offset(0, 2).Value="Last Name"
oXL.activecell.offset(0, 3).Value="E-mail (reply)"
oXL.activecell.offset(0, 4).Value="SAM Account"
oXL.activecell.offset(0, 5).Value="Work Phone"
oXL.activecell.offset(0, 6).Value="Mobile"
oXL.activecell.offset(0, 7).Value="Company"
oXL.activecell.offset(0, 8).Value="Address"
oXL.activecell.offset(0, 9).Value="P. O. Box"
oXL.activecell.offset(0, 10).Value="Postal Code"
oXL.activecell.offset(0, 11).Value="State/Province" 'State/Province
oXL.activecell.offset(0, 12).Value="City" 'city
oXL.activecell.offset(0, 13).Value="Mail alias" 'mail alias
oXL.activecell.offset(0, 14).Value="Description"
oXL.activecell.offset(0, 15).Value="Title"
oXL.activecell.offset(0, 16).Value="Department"
oXL.activecell.offset(0, 17).Value="Country" 'Country
oXL.activecell.offset(0, 18).Value="Home Phone"
oXL.activecell.offset(0, 19).Value="Fax" ' FAX
oXL.activecell.offset(0, 20).Value="Pager"
oXL.activecell.offset(0, 21).Value="Common Name (CN)"
oXL.activecell.offset(0, 22).Value="msExchUseOAB" 'DN of OAL in use by this user
oXL.activecell.offset(0, 23).Value="msExchQueryBaseDN" 'DN of Address list or OU to use for search in OWA
'Put the cursor in the starting cell and read the DS root
oXL.ActiveSheet.range("A2").Activate ' this cell has the DS root in it
'Show it to the user
'WScript.Echo oXL.activecell.Value
'This is the starting point in the ds
oXL.activecell.Value=root
'root = oXL.activecell.Value
'Step to the next row
oXL.activecell.offset(1, 0).Activate
'User input of OU
ou = InputBox("Enter OU(s)" & vbcrlf & "Examples:" & vbcrlf & "ou=users" & vbcrlf & "ou=users,ou=leading,ou=noname", "Dump Cust Users - OU info", "ou=users")
' Evaluate the user input.
If ou = "" Then ' Cancelled by the user
WScript.quit
End If
'Compose the ADSI path...
s = ou + "," + root
'Show it to the user...
'WScript.Echo s
'And get the objects
Set oContainer = GetObject("LDAP://" & s)
DumpInfo oContainer
Sub DumpInfo(oObject)
oObject.Filter = Array("user")
For Each oUser in oObject
oUser.GetInfo
' Start to count
Count=Count + 1
'ou=dummy,ou=testkunde,ou=customers
oXL.activecell.Value=ou
On Error Resume Next
'Fill Excel with info from AD
oXL.activecell.offset(0, 1).Value=oUser.Get("givenName")
oXL.activecell.offset(0, 2).Value=oUser.Get("sn")
oXL.activecell.offset(0, 3).Value=oUser.Get("mail")
oXL.activecell.offset(0, 4).Value=oUser.Get("sAMAccountName")
oXL.activecell.offset(0, 5).Value=oUser.Get("telephoneNumber")
oXL.activecell.offset(0, 6).Value=oUser.Get("mobile")
oXL.activecell.offset(0, 7).Value=oUser.Get("company")
oXL.activecell.offset(0, 8).Value=oUser.Get("streetAddress")
oXL.activecell.offset(0, 9).Value=oUser.Get("PostOfficeBox")
oXL.activecell.offset(0, 10).Value=oUser.Get("postalCode")
oXL.activecell.offset(0, 11).Value=oUser.Get("st") 'State/Province
oXL.activecell.offset(0, 12).Value=oUser.Get("l") 'city
'Get the e-mail address array
aProxy = oUser.ProxyAddresses
For intCount = LBound(aProxy) To UBound(aProxy)
ProxyAddressesList=ProxyAddressesList & aProxy(intCount) & ","
Next
oXL.activecell.offset(0, 13).Value=ProxyAddressesList
ProxyAddressesList=""
oXL.activecell.offset(0, 14).Value=oUser.Get("description")
oXL.activecell.offset(0, 15).Value=oUser.Get("title")
oXL.activecell.offset(0, 16).Value=oUser.Get("department")
oXL.activecell.offset(0, 17).Value=oUser.Get("c") 'Country
oXL.activecell.offset(0, 18).Value=oUser.Get("homePhone")
oXL.activecell.offset(0, 19).Value=oUser.Get("facsimileTelephoneNumber") ' FAX
oXL.activecell.offset(0, 20).Value=oUser.Get("pager")
oXL.activecell.offset(0, 21).Value=oUser.Get("cn")
oXL.activecell.offset(0, 22).Value=oUser.Get("msExchUseOAB")
oXL.activecell.offset(0, 23).Value=oUser.Get("msExchQueryBaseDN")
' Concactenate into givenName_and_sn for to check for minimum requiered info
givenName_and_sn=givenName_ & sn_
' Used to track line in spreasheet with missing mandatory values
LineCount=Count+oucount
'Compose the user common name name from first and last names...
uname = "CN=" + oXL.activecell.offset(0, 1).Value + " " + oXL.activecell.offset(0, 2).Value
' Step to next row
oXL.activecell.offset(1, 0).Activate
Next
End Sub
oXL.activecell.offset(5, 0).Activate
oXL.activecell.Value="e_n_d"
oXL.activecell.font.ColorIndex=xlAutomatic
oXL.activecell.offset(2, 0).Activate
oXL.activecell.Value="Blue = At least one of the cells in the two rows must contain a value when creating a new user"
oXL.activecell.font.bold=True
oXL.activecell.font.ColorIndex=5 'Blue
oXL.activecell.offset(1, 0).Activate
oXL.activecell.Value="Red = Cell value must be present when creating a new user"
oXL.activecell.font.bold=True
oXL.activecell.font.ColorIndex=3 'Red
oXL.activecell.offset(1, 0).Activate
oXL.activecell.Value="Green = Not yet implimented (i.e. column will be ignoreeed)"
oXL.activecell.font.bold=True
oXL.activecell.font.ColorIndex=10 'Green
oXL.activecell.offset(2, 0).Activate
oXL.activecell.Value="e_n_d marks the ending of the scripts traversal of the spreadsheet - anything can be written in the rows/columns after the e_n_d statement"
oXL.activecell.font.ColorIndex=xlAutomatic
oXL.activecell.offset(2, 0).Activate
oXL.activecell.Value="Anything can be written in a row from column B and outwards as long as the beginning of the row (cell in column A) is blank"
oXL.activecell.font.ColorIndex=xlAutomatic
'cleanup
Set oContainer = Nothing
WScript.Echo "Finished - " & count & " user(s) dumped"& vbcrlf & vbcrlf & "Remember to save the Worksheet!!!"
'ou=dummy,ou=testkunde,ou=customers
'Done. close excel spreadsheet
'oXL.application.quit