Data from Excel to Active Directory Question?

[octavian10]

Hello all,
I have the following script which gets User info from an Excel spreadsheet and creates the users in Active Directory.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("D:\Scripts\New_Users.xls")
intRow = 2
Do Until objExcel.Cells (intRow,1).Value = ""
Set objOU = GetObject("LDAP://172.16.5.9/CN=Users, DC=redshift, DC=com")
Set objUser = objOU.Create ("User", "cn=" & objExcel.Cells (intRow, 1).Value)
objUser.SamAccountName = objExcel.Cells (intRow, 2).Value
objUser.GivenName = objExcel.Cells (intRow, 3).Value
objUser.SN = objExcel.Cells (intRow, 4).Value
objUser.SetInfo
intRow = intRow + 1
Loop
objExcel.quit

This works great, but if I run it twice the second time I get a Already Exists error. Which is undestandable. How can I modify the code to update active directory with new users added to this spread sheet so I do not get this error. I am new to scripting and am just doing this to learn but I can see scenerios where somthing like this might come in handy. Hopefully someone can help.
Have a good day.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[dglienna]

Use an ERROR trap. Look up ON ERROR

I'm not sure what it is in VBA

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[tsuji]

[tt][blue]'moved up to outside of the loop[/blue]
Set objOU = GetObject("LDAP://172.16.5.9/CN=Users, DC=redshift, DC=com")
[blue]on error resume next[/blue]
Do Until objExcel.Cells (intRow,1).Value = ""
Set objUser = objOU.Create ("User", "cn=" & objExcel.Cells (intRow, 1).Value)
if err.number=0 then
objUser.SamAccountName = objExcel.Cells (intRow, 2).Value
objUser.GivenName = objExcel.Cells (intRow, 3).Value
objUser.SN = objExcel.Cells (intRow, 4).Value
objUser.SetInfo
[blue]'add some log data if you like
objExcel.cells(intRow,5).value="created"[/blue]
else
[blue]'Decide what to do.
'For instance leave the logged status at (intRow,5) intact for the repeated run,
'that means do nothing (preserving the created status)[/blue]
err.clear
end if
intRow = intRow + 1
Loop
[blue]on error goto 0[/blue]
'continue with the rest...
[/tt]

 

[octavian10]

Thanks alot guy's I will try it as soon as I get behind the test server. I feel kind of stupid because I just read about On Error Resume Next and didnt think to apply it in this scenerio.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[octavian10]

Ok, the code looks like this now but there is somthing wrong. It will only input every other row from the spreadsheet. If none of the users are in AD the script runs fine and 'Created' is added to the spreadsheet, but once that is done running the script again only inserts every other row of the spreadsheet into AD. What is it about this code that works fine on an initial population of AD but any time after that it only gets every other row,

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("D:\Scripts\New_Users.xls")
intRow = 2
Set objOU = GetObject("LDAP://172.16.5.9/CN=Users, DC=redshift, DC=com")
On Error Resume Next
Do Until objExcel.Cells (intRow,1).Value = ""
Set objUser = objOU.Create ("User", "cn=" & objExcel.Cells (intRow, 1).Value)
If err.number=0 Then
objUser.SamAccountName = objExcel.Cells (intRow, 2).Value
objUser.GivenName = objExcel.Cells (intRow, 3).Value
objUser.SN = objExcel.Cells (intRow, 4).Value
objUser.SetInfo
objExcel.cells(intRow,5).value="Created"
Else
err.clear
End If
intRow = intRow + 1
Loop
On Error goto 0
objExcel.quit

Thanks again.


"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[tsuji]

I would say that what you observe or describe can only be a mirage.

 

[tsuji]

In case you do not get the meaning why I reject this scenario: the reason is this. If the statement that running the first time and all is well, then the observation that repeating (2nd run) the script will make it every other row is not realistic. It must mean that the data are actually wrong, one way or the other, in the excel spreedsheet. Two most prominant constraints that should not be violated are [a] the uniqueness of the rdn within the container (here cn=users) which I think should be taken care of; and the uniqueness of samaccountnumber throughout the domain. And it is the latter which may cause you additonal problem and it is inconsistent with the originel assertion that it runs fine the first time (and that was also a reason why I proposed the error trapping at the particular position). If you want to avoid both constraints being by accident or by negligence be violated - the rdn by repetitive execution of the script, or the samaccountname by bad name being assigned - and hence error occurs, you have to trap error at the later stage. Like this.
[tt]
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("D:\Scripts\New_Users.xls")
intRow = 2
Set objOU = GetObject("LDAP://172.16.5.9/CN=Users, DC=redshift, DC=com")
On Error Resume Next
Do Until objExcel.Cells (intRow,1).Value = ""
Set objUser = objOU.Create ("User", "cn=" & objExcel.Cells (intRow, 1).Value)
objUser.SamAccountName = objExcel.Cells (intRow, 2).Value
objUser.GivenName = objExcel.Cells (intRow, 3).Value
objUser.SN = objExcel.Cells (intRow, 4).Value
objUser.SetInfo
'moved down here
If err.number=0 Then
objExcel.cells(intRow,5).value="Created"
Else
err.clear
End If
intRow = intRow + 1
Loop
On Error goto 0
[/tt]
And then the error might be raised for either reason.

 

[octavian10]

I do not know why but it seemed that it worked on the initial data load but after that it took only every other row from excel. That is not the case, after some more testing, the code that I posted yesterday when run, whether the users ar in AD or not, only grabs and writes 'Created' every other row in the spreadsheet. If the sheet had 4 names
test1
test2
test3
test4

only test 2 and 4 are created in AD every time the script is ran.
Thanks for your time.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)