[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

Create contacts in ADS with the details from the excel.

Hi,

I have a excel which has these details
EX:
FirstName      Last Name       Description     External email id.

The Email addresses are a exter emails.I need to add them as the SMTP addresses .
I need to create 50+ Contact. Any easier way of doint this.

Regards
SHarath
0
bsharath
Asked:
bsharath
  • 19
  • 18
1 Solution
 
chandru_solCommented:
Sharath,

Try this.....

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplay

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "domain.com/OU=_ADC ," ' Note the comma
strPathExcel = "z:\Scripts\contacts.xls"
strYourDescription = "Guy's Contact"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplay  = objExcel.cells(intRow, 6).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "targetAddress", "smtp:" & strEmail
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit

Excel file will be as below in each column. i have left a space to indicate each column
strContactName strAlias strEmail strFirst strLast StrDisplay
Second line would be your information...

regards
Chandru
0
 
bsharathAuthor Commented:
Chandru is this a Excel Macro or vbs file
0
 
chandru_solCommented:
its offcourse a vbs file and specify the location in the line

strPathExcel = "z:\Scripts\contacts.xls"

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bsharathAuthor Commented:
What should the StrContactname,StrAlias,StrDisplay
0
 
bsharathAuthor Commented:
How should i specify this line

strOU = "development..co.uk/OU=Contacts ," ' Note the comma
0
 
chandru_solCommented:
StrContactname = name
strAlias = Alias in the contacts attribute
strdisplay = Displayname
0
 
chandru_solCommented:
You can just put in the OU=name of the OU,
0
 
bsharathAuthor Commented:
The line looks like this...
strOU = "OU=Contacts ," ' Note the comma

Is it correct.
Contacts is inside many OU's should i put the full path
0
 
bsharathAuthor Commented:
I get this...

---------------------------
Windows Script Host
---------------------------
Script:      C:\CreateContacts.vbs
Line:      15
Char:      1
Error:      There is no such object on the server.
Code:      80072030
Source:       (null)

---------------------------
OK  
---------------------------
0
 
chandru_solCommented:
You should be putting the full path in the reverse order

Example OU structure below
India
Chennai
Contacts

strOU = "OU=Contacts,OU=Chennai,OU=India,"

does this make sense?
0
 
bsharathAuthor Commented:
Chandru the script works like a charm,

Need some more addings
Can we add the description box,Office,Mobile

These 3 fields from excel dont reflect in the users properties after creation
strEmail       strFirst       strLast
0
 
chandru_solCommented:
Try this version....

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplay

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "domain.com/OU=_ADC ," ' Note the comma
strPathExcel = "z:\Scripts\contacts.xls"
strYourDescription = "Guy's Contact"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplay  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "Description", StrDescription
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit

strdescription stroffice, strmobile
What do you see on the firstname, lastname and email address. Is all the attributes blank?
0
 
bsharathAuthor Commented:
How should the excel file be now...
0
 
chandru_solCommented:
strContactName strAlias strEmail strFirst strLast StrDisplay strdescription stroffice strmobile

The above should be the first line of the excel and each space should the column
0
 
bsharathAuthor Commented:
I get this...

---------------------------
Windows Script Host
---------------------------
Script:      C:\CreateContacts.vbs
Line:      30
Char:      4
Error:      Variable is undefined: 'StrDescription'
Code:      800A01F4
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
0
 
chandru_solCommented:
Whoops! Forgot to Dim the variables.....

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplay, strDescription, stroffice, strmobile

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "domain.com/OU=_ADC ," ' Note the comma
strPathExcel = "z:\Scripts\contacts.xls"
strYourDescription = "Guy's Contact"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplay  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "Description", StrDescription
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit
0
 
bsharathAuthor Commented:
I get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\CreateContacts.vbs
Line:      40
Char:      4
Error:      Unspecified error
Code:      80004005
Source:       (null)

---------------------------
OK  
---------------------------
0
 
chandru_solCommented:
Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplay, strDescription, stroffice, strmobile

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "domain.com/OU=_ADC ," ' Note the comma
strPathExcel = "z:\Scripts\contacts.xls"
strYourDescription = "Guy's Contact"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplay  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   'objContact.Put "Description", StrDescription
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit
0
 
bsharathAuthor Commented:
I get this...

---------------------------
Windows Script Host
---------------------------
Script:      C:\CreateContacts.vbs
Line:      42
Char:      4
Error:      Unspecified error
Code:      80004005
Source:       (null)

---------------------------
OK  
---------------------------
0
 
bsharathAuthor Commented:
Chandru if possible can you remove all except these.

Firstname  Lastname   Emailid   Description   Mobile

0
 
chandru_solCommented:
Here is the working and tested code.....

Atlast managed to install exchange server at home and tested this code...........

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplayName, strDescription, stroffice, strmobile

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "OU=Contacts,OU=Chennai,OU=India," ' Note the comma
strPathExcel = "D:\Scripts\contacts.xls"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplayName  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "DisplayName", StrDisplayName
   objContact.Put "Description", StrDescription
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit

Change the OU and the location of the file name. I have left all the attributes as it may come handy

regards
Chandru
0
 
bsharathAuthor Commented:
Chandru great for installing the exchange...

I get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\CreateContacts.vbs
Line:      40
Char:      4
Error:      Unspecified error
Code:      80004005
Source:       (null)

---------------------------
OK  
---------------------------

I dont know if i am doing some thing wrong
0
 
chandru_solCommented:
Can you check if your excel file has all these information one per column?

   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplayName  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
0
 
bsharathAuthor Commented:
I just have exactly as mentioned..

strContactName       strAlias       strEmail       "strFirst
"      strLast       StrDisplay      strdescription      stroffice      strmobile
Sur      Reddy      bsharath0@yahoo.co.in      ead      Raheja      raj      758      yh      984001
0
 
chandru_solCommented:
In the above we have strdisplayname but you have shown it as strdisplay.

Can you try the new code?
0
 
chandru_solCommented:
StrDisplayName  = objExcel.cells(intRow, 6).Value

objContact.Put "DisplayName", StrDisplayName

These are the two lines that have been changed
0
 
bsharathAuthor Commented:
I have changed the excel as mentioned.

I still get this,

---------------------------
Windows Script Host
---------------------------
Script:      C:\CreateContacts.vbs
Line:      40
Char:      4
Error:      Unspecified error
Code:      80004005
Source:       (null)

---------------------------
OK  
---------------------------
The last comments that you have given should i change anything or you are mentioning that you have changed them in the last script...
0
 
chandru_solCommented:
Try this all changed and tested script....
Change the OU and the location of the file name

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplayName, strDescription, stroffice, strmobile

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "OU=Contacts,OU=Chennai,OU=India," ' Note the comma
strPathExcel = "D:\Scripts\contacts.xls"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplayName  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "DisplayName", StrDisplayName
   objContact.Put "Description", StrDescription
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit
0
 
bsharathAuthor Commented:
The script looks like this after the 2 changes....

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplayName, strDescription, stroffice, strmobile

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "OU=Contacts,OU=User Accounts,OU=IND,OU=Countries," ' Note the comma
strPathExcel = "C:\CreateContacts.xlsx"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   StrDisplayName  = objExcel.cells(intRow, 6).Value
   StrDescription  = objExcel.cells(intRow, 7).Value
   Stroffice  = objExcel.cells(intRow,8).Value
   Strmobile  = objExcel.cells(intRow, 9).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "DisplayName", StrDisplayName
   objContact.Put "Description", StrDescription
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit
0
 
chandru_solCommented:
If possible post the code which you are trying when you get this error....
0
 
chandru_solCommented:
Did you try the script which you have given and you get error?
0
 
chandru_solCommented:
ID: 20110257 post seems fine
0
 
bsharathAuthor Commented:
ID: 20110257 post
I get these errors as mentioned above.

I DONT KNOW WHY...
0
 
chandru_solCommented:
Can you try this as i have removed those you don't want.........

Option Explicit
Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
Dim strOU, strContactName, strPathExcel, strEmail
Dim intRow, strYourDescription, strFirst, strLast, strAlias, StrDisplayName, strDescription, stroffice, strmobile

' Set string variables
' Note: Assume an OU called suppliers exists.
strOU = "OU=Contacts,OU=User Accounts,OU=IND,OU=Countries," ' Note the comma
strPathExcel = "C:\CreateContacts.xlsx"
intRow = 2 ' Row 1 contains headings

' Section to bind to Active Directory
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & objRootLDAP.Get("DefaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

' Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
   strContactName = objExcel.Cells(intRow, 1).Value
   strAlias = objExcel.cells(intRow, 2).Value
   strEmail = objExcel.cells(intRow, 3).Value
   strFirst = objExcel.cells(intRow, 4).Value
   strLast = objExcel.cells(intRow, 5).Value
   Stroffice  = objExcel.cells(intRow,6).Value
   Strmobile  = objExcel.cells(intRow, 7).Value
 
     ' Build the actual contacts.
   Set objContact = objContainer.Create("Contact","cn=" & strContactName)
   objContact.Put "Mail", strEmail
   objContact.Put "givenName", strFirst
   objContact.Put "sn", strLast
   objContact.Put "mailNickname", Cstr(strAlias)
   objContact.Put "proxyAddresses", "SMTP:" & strEmail
   objContact.Put "physicalDeliveryOfficeName", Stroffice
   objContact.Put "mobile", Strmobile
   objContact.SetInfo

intRow = intRow + 1
Loop

Wscript.Echo "Done"
objExcel.Quit

Excel file as below....
strContactName   strAlias   strEmail               strFirst   strLast   Stroffice   Strmobile
Bharath      Bsharath    bsharath@yahoo.co.in        Bharath    s         Chennai       9884098840
0
 
chandru_solCommented:
Sharath,

Did you manage to create the contacts? Did this work fine without errors?
0
 
bsharathAuthor Commented:
Yes Chandru thanks a lot...
0
 
bsharathAuthor Commented:
Any help with the Excel user creation .Did you have time to test...
0
 
The_RickerCommented:
how do you script this to connect to a subdomain?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 19
  • 18
Tackle projects and never again get stuck behind a technical roadblock.
Join Now