Announcement

Collapse
No announcement yet.

Bulk import AD contacts

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Bulk import AD contacts

    Having a slight problem with a script.
    I am using the base code from http://computerperformance.co.uk/ but am adding quiet a few extra items.
    Here is the end result but I am getting error 80004005 on the first objContact
    Here is the script

    Moderator EDIT: "Added [Code] tags", \Rems
    Code:
    ' ContactExcel .vbs
    ' Purpose VBScript to create contacts from a list on names in Excel
    ' Usage where you need contacts for example, suppliers
    ' Author Guy Thomas http://computerperformance.co.uk/
    ' Version 2.2 - May 2005
    ' --------------------------------------------------------------'
    
    Option Explicit
    Dim objRootLDAP, objContainer, objContact, objExcel, objSheet
    Dim strOU, strContactName, strPathExcel, strEmail, strMobile, strWebPage
    Dim intRow, strYourDescription, strFirst, strLast, strPhone
    Dim strCompany, strDepartment, strTitle, strstreetAddress
    Dim strCity, strstate, strpostcode, strFax
    
    
    ' Set string variables
    ' Note: Assume an OU called suppliers exists.
    strOU = "OU=External Contacts ," ' Note the comma
    strPathExcel = "c:\Scripts\external.xls"
    strYourDescription = "External Contacts"
    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
       strEmail = objExcel.cells(intRow, 17).Value
       strFirst = objExcel.cells(intRow, 3).Value
       strLast = objExcel.cells(intRow, 4).Value
       strCompany = objExcel.cells(intRow, 5).Value
       strDepartment = objExcel.cells(intRow, 6).Value
       strTitle = objExcel.cells(intRow, 7).Value
       strstreetAddress = objExcel.cells(intRow, 8).Value
       strCity = objExcel.cells(intRow, 9).Value
       strstate = objExcel.cells(intRow, 12).Value
       strpostcode = objExcel.cells(intRow, 11).Value
       strFax = objExcel.cells(intRow, 13).Value
       strPhone = objExcel.cells(intRow, 14).Value
       strMobile = objExcel.cells(intRow, 15).Value
       strMobile = objExcel.cells(intRow, 15).Value
       strWebPage = objExcel.cells(intRow, 19).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 "Company", strCompany
       objContact.Put "Department", strDepartment
       objContact.Put "Title", strTitle
       objContact.Put "streetAddress", strstreetAddress
       objContact.Put "l", strCity
       objContact.Put "st", strstate
       objContact.Put "postalCode", strpostcode
       objContact.Put "facsimilyTelephoneNumber", strFax
       objContact.Put "telephoneNumber", strPhone
       objContact.Put "mobile", strMobile
       objContact.Put "wWWHomePage", strWebPage
    
    
       objContact.SetInfo
    intRow = intRow + 1
    Loop
    objExcel.Quit
    
    WScript.Quit
    
    ' End of Sample ContactExcel VBScript
    Thanks for the help
    Last edited by Rems; 11th September 2008, 20:06.

  • #2
    Re: Bulk import AD contacts

    Just for testing,
    use your script, but replace the Do-Loop with this loop:

    Code:
    Do Until (objExcel.Cells(intRow,1).Value) = ""
       strContactName = objExcel.Cells(intRow, 1).Value
       strEmail = objExcel.cells(intRow, 17).Value
       strFirst = objExcel.cells(intRow, 3).Value
       strLast = objExcel.cells(intRow, 4).Value
       strCompany = objExcel.cells(intRow, 5).Value
       strDepartment = objExcel.cells(intRow, 6).Value
       strTitle = objExcel.cells(intRow, 7).Value
       strstreetAddress = objExcel.cells(intRow, 8).Value
       strCity = objExcel.cells(intRow, 9).Value
       strstate = objExcel.cells(intRow, 12).Value
       strpostcode = objExcel.cells(intRow, 11).Value
       strFax = objExcel.cells(intRow, 13).Value
       strPhone = objExcel.cells(intRow, 14).Value
       strMobile = objExcel.cells(intRow, 15).Value
       strMobile = objExcel.cells(intRow, 15).Value
       strWebPage = objExcel.cells(intRow, 19).Value
    
       ' echo Data for each contact.
       wscript.echo "objContainer =", objContainer.DistinguishedName & vbNewLine & _
        vbNewLine & "cell(" & intRow & ",1) Common-Name =", strContactName & _
        vbNewLine & "cell(" & intRow & ",17) strEmail =", strEmail & _
        vbNewLine & "cell(" & intRow & ",3) strFirst =", strFirst & _
        vbNewLine & "cell(" & intRow & ",4) strLast =", strLast & _
        vbNewLine & "cell(" & intRow & ",5) strCompany =", strCompany & _
        vbNewLine & "cell(" & intRow & ",6) strDepartment =", strDepartment & _
        vbNewLine & "cell(" & intRow & ",7) strTitle =", strTitle & _
        vbNewLine & "cell(" & intRow & ",8) strstreetAddress =", strstreetAddress & _
        vbNewLine & "cell(" & intRow & ",9) strCity =", strCity & _
        vbNewLine & "cell(" & intRow & ",12) strstate =", strstate & _
        vbNewLine & "cell(" & intRow & ",11) strpostcode =", strpostcode & _
        vbNewLine & "cell(" & intRow & ",13) strFax =", strFax & _
        vbNewLine & "cell(" & intRow & ",14) strPhone =", strPhone & _
        vbNewLine & "cell(" & intRow & ",15) strMobile =", strMobile & _
        vbNewLine & "cell(" & intRow & ",15) strMobile =", strMobile & _
        vbNewLine & "cell(" & intRow & ",19) strWebPage =", strWebPage
    
       intRow = intRow + 1
    Loop
    You are running the script as a member of Domain Admins.
    Does an object with that name already exist ? Are there any signs used in the cname of any of the contacts?

    \Rems
    Last edited by Rems; 14th September 2008, 18:37. Reason: objContainer.DistinguishedName

    This posting is provided "AS IS" with no warranties, and confers no rights.

    __________________

    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts

    Comment


    • #3
      Re: Bulk import AD contacts

      Originally posted by Rems View Post
      J

      You are running the script as a member of Domain Admins.
      Does an object with that name already exist ? Are there any signs used in the cname of any of the contacts?

      \Rems
      Yep, running logged in as a domain admin
      No obects exist with the same cn
      No symbols are being used in the cn, all test with no spaces as well.
      I'll try yours out tomorrow, thanks for the help

      Comment


      • #4
        Re: Bulk import AD contacts

        Originally posted by Matt_Cline View Post
        I'll try yours out tomorrow, thanks for the help
        I just corrected a typo in the test loop!
        My Loop does not create the contacts, it'll just echo the data that is used to create contacts so you can check if there could be a problem.

        Be sure you have not a line On Error Resume Next somewhere in your script.


        \Rems

        This posting is provided "AS IS" with no warranties, and confers no rights.

        __________________

        ** Remember to give credit where credit's due **
        and leave Reputation Points for meaningful posts

        Comment


        • #5
          Re: Bulk import AD contacts

          Here is what I have used in the past for my loop, but I do have "on error resume next" in the script because I did get some errors, but it does create the contacts.

          Code:
          Do Until (objExcel.Cells(intRow,1).Value) = ""
             
          strContactName = objExcel.Cells(intRow, 1).Value
          strFirst = objExcel.cells(intRow, 2).Value
          strMiddle = objExcel.cells(intRow, 3).Value
          strLast = objExcel.cells(intRow, 4).Value
          strDep = objExcel.cells(intRow, 5).Value
          strHomePhone = objExcel.cells(intRow, 6).Value
          strMobilePhone = objExcel.cells(intRow, 7).Value
          strFax = objExcel.cells(intRow, 8).Value
          strCity = objExcel.cells(intRow, 9).Value
          strZip = objExcel.cells(intRow, 10).Value
          strCountry = objExcel.cells(intRow, 11).Value
          strTitle = objExcel.cells(intRow, 12).Value
          strOfficePhone = objExcel.cells(intRow, 13).Value
          strState = objExcel.cells(intRow, 14).Value
          strCompany = objExcel.cells(intRow, 15).Value
          strDescription = objExcel.cells(intRow, 16).Value
          strStreet = objExcel.cells(intRow, 17).Value
          strMailEnable = objExcel.cells(intRow, 18).Value
          strEmail = objExcel.cells(intRow, 19).Value
           
          Set objContact = objContainer.Create("Contact","cn=" _
          & Replace(strContactName,",","\,"))
          
          if not len(strFirst) = 0 then
          objContact.Put "givenName", strFirst
          end if
          
          if not len(strMiddle) = 0 then
          objContact.Put "initials", strMiddle
          end if
          
          if not len(strLast) = 0 then
          objContact.Put "sn", strLast
          end if
          
          if not len(strDep) = 0 then
          objContact.Put "department", strDep
          end if
          
          if not len(strHomePhone) = 0 then
          objContact.Put "homePhone", strHomePhone
          end if
          
          if not len(strMobilePhone) = 0 then
          objContact.Put "mobile", strMobilePhone
          end if
          
          if not len(strFax) = 0 then
          objContact.Put "facsimileTelephoneNumber", strFax
          end if
          
          if not len(strCity) = 0 then
          objContact.Put "l", strCity
          end if
          
             'if not len(strZip) = 0 then
             'objContact.Put "postalcode", strZip    'this wasn't working for me
             'end if
          
          if not len(strCountry) = 0 then
          objContact.Put "c", strCountry
          end if
          
          if not len(strTitle) = 0 then
          objContact.Put "title", strTitle
          end if
          
          if not len(strOfficePhone) = 0 then
          objContact.Put "telephoneNumber", strOfficePhone
          end if
          
          if not len(strState) = 0 then
          objContact.Put "st", strState
          end if
          
          if not len(strCompany) = 0 then
          objContact.Put "company", strCompany
          end if
          
          if not len(strDescription) = 0 then
          objContact.Put "description", strDescription
          end if
          
          if not len(strStreet) = 0 then
          objContact.Put "streetAddress", strStreet
          end if
          
          if not len(strMailEnable) = 0 then
          objContact.MailEnable strEmail
          end if
          
          objContact.SetInfo
          
          intRow = intRow + 1
          Loop

          Comment

          Working...
          X