Announcement

Collapse
No announcement yet.

Excel Contact Script

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

  • Excel Contact Script

    I have this script that will import the contacts from excel into AD....but when it comes into a blank cell where there is no information for lets say the phone number of that contact, then it errors out. Does anyone know how to skip the part where it "puts" the information into the contact if that value in the cell is blank? Here is my code...

    Code:
    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
    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
    strState = objExcel.cells(intRow, 11).Value
    strEmail = objExcel.cells(intRow, 12).Value
    strTitle = objExcel.cells(intRow, 13).Value
    strMemberOf = objExcel.cells(intRow, 14).Value
    strOfficePhone = objExcel.cells(intRow, 15).Value
    strStreet = objExcel.cells(intRow, 16).Value
    strCompany = objExcel.cells(intRow, 17).Value
    strTitle2 = objExcel.cells(intRow, 18).Value
    strStreet2 = objExcel.cells(intRow, 19).Value
    strMailEnable = objExcel.cells(intRow, 20).Value
    strAlias = objExcel.cells(intRow, 21).Value
    strTargetAddress = objExcel.cells(intRow, 22).Value
     
    Set objContact = objContainer.Create("Contact",_
       "cn=" & strContactName)
    
    objContact.Put "givenName", strFirst
    objContact.Put "initials", strMiddle
    objContact.Put "sn", strLast
    objContact.Put "department", strDep
    objContact.Put "homePhone", strHomePhone
    objContact.Put "mobile", strMobilePhone
    objContact.Put "facsimileTelephoneNumber", strFax
    objContact.Put "l", strCity
    objContact.Put "postalCode", strZip
    objContact.Put "c", strState
    objContact.Put "mail", strEmail
    objContact.Put "title", strTitle
    objContact.Put "memberOf", strMemberOf
    objContact.Put "telephoneNumber", strOfficePhone
    objContact.Put "st", strStreet
    objContact.Put "company", strCompany
    objContact.Put "description", strTitle2
    objContact.Put "streetAddress", strStreet2
    objContact.Put "EnableExchangeMail", strMailEnable
    objContact.Put "mailNickname", strAlias
    objContact.Put "targetAddress", strTargetAddress
    objContact.SetInfo
    
    intRow = intRow + 1
    Loop
    objExcel.Quit

  • #2
    Re: Excel Contact Script

    I've tried using "On Error Resume Next"...but that doesn't really skip it. It completes the script with that but it doesn't put the contact in AD.

    Comment


    • #3
      Re: Excel Contact Script

      ok.. im not a VB master by any means, but i have a line you could try. i had to consult with my resident VB expert, and he says it *should* work...

      Code:
       If ((strFirst <> "") And (strFirst <> Nothing)) Then objContact.Put "givenName", strFirst
      try that out and let me know, cause its an interesting script. it would be nice to use to populate test domains...
      its easier to beg forgiveness than ask permission.
      Give karma where karma is due...

      Comment


      • #4
        Re: Excel Contact Script

        Thanks...so I should put that for every string?

        Comment


        • #5
          Re: Excel Contact Script

          I tried it and I'm getting a "variable object not set" error.

          Comment


          • #6
            Re: Excel Contact Script

            I've commented out some of the errors that I'm seeing with the blank cells...and it seems like its still not going through. The last error that i recieved after commenting out the blank cells....is for the last part: "objContact.SetInfo". It says "The specified directory service attribute or value does not exist."

            Comment


            • #7
              Re: Excel Contact Script

              Wrap each of the puts inside an if to check the string has a length e.g.:
              Code:
              if not len(strHomePhone) = 0 then
                 objContact.Put "homePhone", strHomePhone
              end if
              Tom Jones
              MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
              PhD, MSc, FIAP, MIITT
              IT Trainer / Consultant
              Ossian Ltd
              Scotland

              ** Remember to give credit where credit is due and leave reputation points where appropriate **

              Comment


              • #8
                Re: Excel Contact Script

                That seems to work, thanks Ossian...I am still getting the error with "objContact.SetInfo".

                "The specified directory service attribute or value does not exist."

                Comment


                • #9
                  Re: Excel Contact Script

                  Originally posted by ekrengel View Post
                  That seems to work, thanks Ossian...I am still getting the error with "objContact.SetInfo".

                  "The specified directory service attribute or value does not exist."

                  Check all the names you are using in your put statements (try adding them in one by one until an error occurs)
                  Tom Jones
                  MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                  PhD, MSc, FIAP, MIITT
                  IT Trainer / Consultant
                  Ossian Ltd
                  Scotland

                  ** Remember to give credit where credit is due and leave reputation points where appropriate **

                  Comment


                  • #10
                    Re: Excel Contact Script

                    Ok so I went through them all...and I'm getting the directory service error from:

                    Code:
                    if not len(strMailEnable) = 0 then
                    objContact.Put "EnableExchangeMail", strMailEnable
                    end if
                    I messed around with it and researched a little and found that this works...

                    Code:
                    if not len(strMailEnable) = 0 then
                    objContact.MailEnable strTargetAddress
                    end if

                    Thanks for the help.

                    Comment


                    • #11
                      Re: Excel Contact Script

                      Oh...I have 1 more questions....if the contact already exists...how do I get it to update it instead of erroring out saying that it already exists.

                      Comment


                      • #12
                        Re: Excel Contact Script

                        It looks like On Error Resume next will work for this...

                        Comment


                        • #13
                          Re: Excel Contact Script

                          Actually...it doesn't update the current contact, it just skips over it...which is alright but is there a way to make it update?

                          Comment


                          • #14
                            Re: Excel Contact Script

                            Actually...it doesn't update the current contact, it just skips over it...which is alright but is there a way to make it update?
                            You cannot modify existing objects (in this case the objects are 'Contacts') with that script, because you had to use the "Create" option in the line:
                            Set objContact = objContainer.Create("Contact","cn=" & strContactName) for creation of the new objects. Then you use that "objContact" to .Put the values in.

                            When an object already exists... it must be found first for opening and changing values. That demands a differend approache, which uses the "Get" option.

                            (download: "ADSI ScriptOMatic" to see the difference in code between create an Object and write an ADSI Object class.
                            And, maybe this example2 can be usefull to your script)

                            If you want to do both with the same script, you must find first a way to determine if the Contact is new, or if it already exist before you can use '.put'
                            There are several methods, I think for your script you best can use 'Error handling'.
                            You already put: On Error Resume Next somewhere at the beginning of the script.
                            Next is to make sure the err.number is set to 0 by adding the line: Err.Clear just before the line:
                            Set objContact = objContainer.Create("Contact","cn=" & strContactName)
                            After that 'SET...'-line, add the line: If Err.number = 0 Then
                            Continue your original script thats writing the values to the new contacts.
                            And, just after the last line which containing: objContact.SetInfo in your original script, you add the line: End If

                            Now you have created the possibility to put in an ELSE condition to the script in cases the Error code is not equal to 0.
                            I'm not sure but I think the error code for "object already created" is Err.number = "-2147019886" (use a msgBox and analize the error numbers from your script). Use the error number with the ELSE condition:
                            Else If Err.number = "-2147019886" Then
                            now from here, paste lines for changing values of the existing Contacts (when using any "Const ADS_... statements", place these before the loop! so they are read only once).
                            (EDIT: Because this added 'object update' part also starts with IF, you must end this part also with END IF - OR - you could also use: ElseIf instead of Else IF , in that case you must not add an extra End IF)

                            Note:
                            Normally an object-'modifying'-script is used only for changing specific existing value(s). What can go wrong in your script is that if you left a cell blanc it will write nul to the object instead of skipping the field, so the original value will be erased! that could be a reason not to combine those two scripts.


                            \Rems
                            Last edited by Rems; 3rd June 2007, 21:13. Reason: ElseIF instead of Else IF | 2ndtime typo's

                            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


                            • #15
                              Re: Excel Contact Script

                              Thanks for the information Rems. I will mess around with what you gave me and let you know how it goes.

                              Comment

                              Working...
                              X