{"id":83,"date":"2007-12-19T09:46:44","date_gmt":"2007-12-19T09:46:44","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=83"},"modified":"2007-12-19T10:40:35","modified_gmt":"2007-12-19T10:40:35","slug":"import-user-attributes-in-ad-from-excel-using-cns","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/code-snippets\/import-user-attributes-in-ad-from-excel-using-cns","title":{"rendered":"vbscript: Import User attributes in AD from Excel – using CNs"},"content":{"rendered":"


\n'************************************************************************************
\n' import2AD.vbs
\n'
\n' usage: cscript import2AD.vbs excel_file
\n'
\n' This script reads a list of user CNs, attributes and values from an Excel spreadsheet.
\n' It then modifies the user in Active Directory - setting attribute = value
\n'
\n' Written by Carol Wapshere
\n' 18th December 2007
\n'
\n'************************************************************************************<\/p>\n

Dim arrCNs()
\nDim arrAttribs()
\nDim arrValues()
\nSet fso = CreateObject(\"Scripting.FileSystemObject\")
\nset objArgs = WScript.Arguments<\/p>\n

'*** Usage ***
\nIf WScript.Arguments.Count <> 1 Then
\nUsage
\nElseIf objArgs(0) = \"\/?\" Then
\nUsage
\nElse
\nExcelFileName = objArgs(0)
\nEnd If<\/p>\n

'*** If no path given assume current folder ***
\nIf InStr(ExcelFileName, \"\\\") = 0 Then
\nExcelFileName = fso.GetAbsolutePathName(\"\") & \"\\\" & ExcelFileName
\nEnd If<\/p>\n

'*** Populate arrays from spreadsheet ***
\nGetColumn arrCNs, ExcelFileName, 1
\nGetColumn arrAttribs, ExcelFileName, 2
\nGetColumn arrValues, ExcelFileName, 3<\/p>\n

'*** Check column lengths ***
\nIf Ubound(arrCNs) <> Ubound(arrAttribs) Or Ubound(arrCNs) <> Ubound(arrValues) Then
\nErrorHandler(\"Column lengths are uneven\")
\nEnd If<\/p>\n

'*** Loop through the CNs, setting attribute values ***
\nFor i = 0 To Ubound(arrCNs)
\nIf FindDN(arrCNs(i), userDN) = 1 Then
\nUpdateAttrib userDN, arrCNs(i), arrAttribs(i), arrValues(i)
\nEnd If
\nNext<\/p>\n

'************************************************************************************
\n' SUBROUTINES
\n'************************************************************************************<\/p>\n

Sub GetColumn(arrExcelValues,ExcelFileName,n)<\/p>\n

Set objExcel = CreateObject(\"Excel.Application\")
\nSet objWorkbook = objExcel.Workbooks.Open(ExcelFileName)
\nobjExcel.Visible = True<\/p>\n

i = 1
\nx = 0<\/p>\n

Do Until objExcel.Cells(i, n).Value = \"\"
\nReDim Preserve arrExcelValues(x)
\narrExcelValues(x) = objExcel.Cells(i, n).Value
\ni = i + 1
\nx = x + 1
\nLoop<\/p>\n

objExcel.Quit<\/p>\n

End Sub<\/p>\n

Function FindDN(CN, DN)<\/p>\n

On Error Resume Next
\nConst ADS_SCOPE_SUBTREE = 2<\/p>\n

Set objConnection = CreateObject(\"ADODB.Connection\")
\nSet objCommand = CreateObject(\"ADODB.Command\")
\nobjConnection.Provider = \"ADsDSOObject\"
\nobjConnection.Open \"Active Directory Provider\"
\nSet objCommand.ActiveConnection = objConnection<\/p>\n

objCommand.Properties(\"Page Size\") = 1000
\nobjCommand.Properties(\"Searchscope\") = ADS_SCOPE_SUBTREE<\/p>\n

objCommand.CommandText = _
\n\"SELECT AdsPath FROM 'LDAP:\/\/dc=fabrikam,dc=com' WHERE objectClass='user'\"_
\n& \" AND Name='\" & CN & \"'\"
\nSet objRecordSet = objCommand.Execute<\/p>\n

If objRecordSet.RecordCount = 0 Then
\nwscript.echo CN & \" not found\"
\nFindDN = 0
\nElseIf objRecordSet.RecordCount > 1 Then
\nwscript.echo \"More than one user object found with the name \" & CN
\nFindDN = 0
\nElse
\nFindDN = 1
\nobjRecordSet.MoveFirst
\nDN = objRecordSet.Fields(\"AdsPath\").Value
\nEnd If
\nEnd Function<\/p>\n

Sub UpdateAttrib(DN, CN, attrib, value)
\nOn Error Resume Next<\/p>\n

Set objUser = GetObject(DN)<\/p>\n

If objUser.Name=\"\" Then
\nwscript.echo DN & \" not found\"
\nElse
\nobjUser.Put attrib, value
\nobjUser.SetInfo
\nSet objUser = GetObject(DN)
\nIf objUser.Get(attrib) = value Then
\nwscript.echo CN & \", \" & attrib & \" successfully updated.\"
\nEnd If
\nEnd If<\/p>\n

End Sub<\/p>\n

Sub Usage
\nwscript.echo
\nwscript.echo \"This script imports values from an Excel spreadsheet into AD.\"
\nwscript.echo
\nwscript.echo \"It will OVERWRITE the current value, \"
\nwscript.echo \"and must be used for SINGLE-VALUED attributes only.\"
\nwscript.echo
\nwscript.echo \"usage: cscript import2AD spreadsheet\"
\nwscript.echo
\nwscript.echo \"The spreadsheet must have THREE columns, in the following configuration:\"
\nwscript.echo \"Column 1: Object CN\"
\nwscript.echo \"Column 2: Attribute Name\"
\nwscript.echo \"Column 3: New Value\"
\nwscript.echo
\nwscript.echo \"The spreadsheet should not have blank rows or column headings.\"
\nWScript.Quit(0)
\nEnd Sub<\/p>\n

Sub ErrorHandler(ErrorMessage)
\nwscript.echo \"Error: \" & ErrorMessage
\nWScript.Quit(1)
\nEnd Sub<\/p>\n

<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

‘************************************************************************************ ‘ import2AD.vbs ‘ ‘ usage: cscript import2AD.vbs excel_file ‘ ‘ This script reads a list of user CNs, attributes and values from an Excel spreadsheet. ‘ It then modifies the user in Active Directory – setting attribute = value ‘ ‘ Written by Carol Wapshere ‘ 18th December 2007 ‘ ‘************************************************************************************ Dim arrCNs() Dim…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":30,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-83","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-1l","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=83"}],"version-history":[{"count":0,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/83\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/30"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}