نمایش نتایج: از شماره 1 تا 2 از مجموع 2

موضوع: Creating Exchange Users with Excel

  
  1. #1
    نام حقيقي: 1234

    مدیر بازنشسته
    تاریخ عضویت
    Jul 2009
    محل سکونت
    5678
    نوشته
    5,634
    سپاسگزاری شده
    2513
    سپاسگزاری کرده
    272

    Creating Exchange Users with Excel

    کد:
    http://www.msexchange.org/articles/Creating-Exchange-Users-Excel.html

    PART-1



    Amit Zinman

    Excel and the rest of the Office family support VBA, which is a language somewhere between VBScript and Visual Basic 6 which provides Office objects but can also access any API present on the system with the right DLL. If you're unfamiliar with programming lingo, this means that VBA can do just about anything once you understand how it works.
    Unlike VBScript, VBA comes with a built-in runtime compiler which goes through the logic of a macro (which is how scripts are called in VBA) in the same way that Visual Basic does. VBA, while not exactly free, is built-in to any Office application so you don't need to install a separate environment such as Visual Studio. This makes VBA the ideal scripting/programming environment. There were a few occasions where I chose to migrate more complex VBScript scripts into Excel or Word, just for the sake of being able to trace through the inevitable programming mistakes.
    Preparations

    First thing to do is open a list of users such as can be seen below.
    My list doesn''t include a description row, but if it had one that would also be acceptable.

    Figure 1
    The latest Office applications come with Macro protection that is supposed to protect you from viruses and other malicious software. However, it also disables Office macros. Before beginning your work you should check that your antivirus is updated (and that it is a good one). Then, you should disable Macro security.

    Figure 2

    Figure 3
    If you're really afraid of Macros, once you're finished with your macro, you can choose whichever setting suits your environment most.
    To go to the VBA environment you have a choice of two (!) environments. For the purposes of this article we will use the Visual Basic Editor, accessible by pressing Alt + F11. What's great about VBA is that it also records your actions within Excel into a regular VBA macro that you can use. This means that instead of buying books or going through Internet newsgroups you can just record a macro of a certain activity if you don't know how to recreate it in a Macro.
    Since our macro will focus on Active Directory and Exchange we need to tell VBA which to use the Exchange and AD APIs, in the same way this is done with Visual Basic 6. To do so choose from the VBE menu, Tools > References.

    Figure 4
    Selecting the Exchange and ADSI type libraries will enable VBA to access their APIs.
    If you do not see the reference libraries above this might mean that you are running Excel on a workstation or a server that does not have Exchange System Manager installed. You should install the latest ESM and Exchange service pack before running any Macro.
    For the purposes of this article I created an OU called 'Test' using the Active Directory Users and Computers console and two departmental groups, dept1 and dept2.

    Figure 5
    The Macro

    A Macro is infact a subroutine similar to those used in Visual Basic.
    Here is the full script. It takes the information entered in the Excel spreadsheet and converts it into Exchange users. Without much scripting knowledge you can tweak it and use it in your own environment.
    کد:
    Sub CreateUsers() Dim Row As Integer Dim oMailbox As CDOEXM.IMailboxStore Dim oUser As IADsUser Set rootDSE = GetObject(LDAP://RootDSE) DomainContainer = rootDSE.Get("defaultNamingContext") Set oOU = GetObject(LDAP://OU=Test,DC=mycompany,DC=local) Row = 1 Do Until Cells(Row, 1) = Empty gname = Trim(Cells(Row, 1).Value) sname = Trim(Cells(Row, 2).Value) ID = Cells(Row, 3).Value mailingaddress = Cells(Row, 4).Value city = Cells(Row, 5).Value postalcode = Cells(Row, 6).Value homephone = Cells(Row, 7).Value cellular = Cells(Row, 8).Value dept = Trim(Cells(Row, 9).Value)
    FullName = gname & " " & sname
    AliasCount = 2 Alias = LCase(gname & Left(sname, AliasCount)) Set conn = CreateObject("ADODB.Connection") conn.Provider = "ADSDSOObject" conn.Open "ADs Provider" ldapStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree" Set rs = conn.Execute(ldapStr) While rs.RecordCount > 0 AliasCount = AliasCount + 1 Alias = LCase(gname & Left(sname, AliasCount)) ldapStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree" Set rs = conn.Execute(ldapStr) Wend ' Update User Record Set oUser = oOU.Create("user", "cn=" & FullName) oUser.Put "cn", FullName oUser.Put "SamAccountName", Alias oUser.Put "userPrincipalName", Alias & "@mycompany.local" oUser.Put "givenName", gname oUser.Put "sn", sname oUser.Put "description", ID oUser.SetInfo oUser.GetInfo ' Enable Account oUser.AccountDisabled = False ' Set Pwd to be same as 123456 oUser.SetPassword ("123456") 'Account is not disabled oUser.AccountDisabled = False ' User must change password at next Logon oUser.Put "pwdLastSet", CLng(0) oUser.SetInfo Set oMailbox = oUser MDBName = "Mailbox Store (EXCHANGE)" StorageGroup = "First Storage Group" Server = "Exchange" AdminGroup = "MyCompany" Organization = "MyCompany School of Arts" DomainDN = "DC=mycompany,DC=local" oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _ ",CN=" & StorageGroup & _ ",CN=InformationStore" & _ ",CN=" & Server & _ ",CN=Servers" & _ ",CN=" & AdminGroup & _ ",CN=Administrative Groups" & _ ",CN=" & Organization & _ ",CN=Microsoft Exchange,CN=Services" & _ ",CN=Configuration," & DomainDN oUser.SetInfo StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local" Set objGroup1 = GetObject(StrobjGroup1) objGroup1.Add (oUser.ADsPath) Set oUser = Nothing Row = Row + 1 Loop End Sub
    How the Script Works

    First of all, you need to declare objects. VBA Objects are defined in the same way done in VB6. That said, you can also use undeclared objects though this doesn't work well with complex ones.
    For the purposes of this macro we declare a mailbox object (oMailbox) and a user object (IADsUser).
    Dim Row As Integer
    Dim oMailbox As CDOEXM.IMailboxStore
    Dim oUser As IADsUser
    As mentioned in my previous programming articles, there are various ways of creating users and their mailboxes. A common one is creating a user using ADSI and then substituting it with an Exchange mailbox object and creating the mailbox using CDOEXM. The nice thing about being able to declare objects is that you always know which object is which rather than letting the runtime compiler decide for you which can sometimes be tricky with VBScripting.
    The next part of the script obtains the domain container in Active Directory, and the OU in which we aim to place the users.
    کد:
    Set rootDSE = GetObject(LDAP://RootDSE) DomainContainer = rootDSE.Get("defaultNamingContext") Set oOU = GetObject(LDAP://OU=Test,DC=mycompany,DC=local)
    Now let's look at the next lines of code:
    Row = 1 Do Until Cells(Row, 1) = Empty gname = Trim(Cells(Row, 1).Value) sname = Trim(Cells(Row, 2).Value) ID = Cells(Row, 3).Value mailingaddress = Cells(Row, 4).Value city = Cells(Row, 5).Value postalcode = Cells(Row, 6).Value homephone = Cells(Row, 7).Value cellular = Cells(Row, 8).Value dept = Trim(Cells(Row, 9).Value) FullName = gname & " " & sname AliasCount = 2 Alias = LCase(gname & Left(sname, AliasCount))
    We use a "Do" loop in order to go through the Excel sheet until an empty row is discovered. The Cells VBA function is used to extract information from the Excel cells.
    For example, "gname" which we use to store the given name of a user obtained from the first cell in each row. I use trim because sometimes, when information is manually entered, empty spaces find their way into the cells which can cause problems later on when creating the username and alias for the user.
    Speaking of the alias for the user, the subroutine constructs it from the given name of the user and the first two letters of the surname of the user (obtained by using the Left function.) This is quite common in many organizations for creating aliases and usernames which need to be short, yet unique. However, if you have two users with same given name and first two letters in the surname you will need to add more letters to the username. The next lines of code achieve this.
    کد:
     Set conn = CreateObject("ADODB.Connection")
        conn.Provider = "ADSDSOObject"
        conn.Open "ADs Provider" 
        ldapStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
         Set rs = conn.Execute(ldapStr)
         While rs.RecordCount > 0
          AliasCount = AliasCount + 1
          Alias = LCase(gname & Left(sname, AliasCount))
          ldapStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
         Set rs = conn.Execute(ldapStr)
         Wend
    The subroutine queries Active Directory for users with the same alias. If it finds any, the AliasCount variable is incremented by one and another letter is added.
    Of course, these lines of code are not perfect. For example, they do not account for users with the same name and surname. You can, for example, enhance the code by adding a number instead of more letters to the alias.
    Once the alias field has been decided on you can create the user in Active Directory. Note again that the Alias and the username (SamAccountName) fields are the same. The description field is used to enter the numerical ID for the user, typically a social security or internal employee number.
    کد:
    Set oUser = oOU.Create("user", "cn=" & FullName)
    oUser.Put "cn", FullName
    oUser.Put "SamAccountName", Alias
    oUser.Put "userPrincipalName", Alias & "@mycompany.local"
    oUser.Put "givenName", gname
    oUser.Put "sn", sname
    oUser.Put "description", ID
    A user is created with the general password "123456" which a user is required to change upon first logon. In a more security oriented organization you might choose to generate a different password.
    کد:
    oUser.AccountDisabled = False
    oUser.SetPassword ("123456")
    oUser.AccountDisabled = False
    oUser.Put "pwdLastSet", CLng(0)
    oUser.SetInfo
    Now, like good Exchange scripting magicians, we perform the world famous switch from AD object to Exchange Mailbox object and create the mailbox.
    کد:
    Set oMailbox = oUser MDBName = "Mailbox Store (EXCHANGE)" StorageGroup = "First Storage Group" Server = "Exchange" AdminGroup = "MyCompany" Organization = "MyCompanyOrg DomainDN = "DC=mycompany,DC=local" oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _ ",CN=" & StorageGroup & _ ",CN=InformationStore" & _ ",CN=" & Server & _ ",CN=Servers" & _ ",CN=" & AdminGroup & _ ",CN=Administrative Groups" & _ ",CN=" & Organization & _ ",CN=Microsoft Exchange,CN=Services" & _ ",CN=Configuration," & DomainDN oUser.SetInfo
    To use the script you would have to modify the fields to suit your Exchange environment. Please note also that some fields are only available once the Exchange mailbox is actually created which might take time because the RUS needs to stamp it. For example, if you want to use one of the Exchange extended attributes to store the User's ID instead of the description you would have to make the script activate the RUS or write another script and activate it a few minutes after the first one is run.
    The rest of the code adds the user to a group specified in the Excel sheet and forwards the Row counter to the next row. Then the loop is closed and the subroutine is ended.
    کد:
    StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local"
        Set objGroup1 = GetObject(StrobjGroup1)
        objGroup1.Add (oUser.ADsPath) 
        Set oUser = Nothing
        Row = Row + 1
    Loop
     End Sub
    Conclusion

    A few lines of code in an Excel VBA, at least theoretically, could save you hours of manually entering users. The main thing about scripts is their flexibility. Any field can be set according to a business logic of your choosing. While the macro illustrated in the article is pretty basic it can grow to infinite complexity. You can use it to synchronize two systems, set permissions and much more, according to specifically tailored rules.





    موضوعات مشابه:
    ویرایش توسط patris1 : 2010-02-02 در ساعت 03:35 PM

  2. #2
    نام حقيقي: 1234

    مدیر بازنشسته
    تاریخ عضویت
    Jul 2009
    محل سکونت
    5678
    نوشته
    5,634
    سپاسگزاری شده
    2513
    سپاسگزاری کرده
    272
    کد:
    http://www.msexchange.org/articles/Creating-Exchange-Users-Excel-Part2.html
    PART-2

    Introduction

    In the previous article I showed how using VBA can save you time when bulk adding users into Active Directory and creating Exchange mailboxes for them. However, there are some limitations to that script because some fields only become available when the mailbox is stamped by the Exchange RUS. Also, the script is a one way, a one time deal. In this article I will show you how to overcome those problems and how to synchronize an Excel sheet with Active Directory.
    Activating the RUS

    Remember the student list we had in the previous article?

    Figure 1
    The second column contained the social security number of the student. I decided to put in the description attribute of the user. However, since the description is a useful field that I can use for identifying roles of teachers and other school workers, let's decide now to move the social security number to the first Extension Attribute of the user, extensionAttribute1.
    Though this attribute supposedly exists for all users once the schema has been extended for Exchange use, you won't be able to set it unless the user has been stamped by the RUS, enabling this attribute.
    Luckily for us, we can help speed this process up. This works best in a single Exchange environment where user management and replication is fast and easy. In a more complex environment the script should point to the domain controller which the RUS uses.
    Let's look closely at the following code:
    کد:
    Sub FireRUS
    'Activate the RUS stamping
    
    Dim RootDse
    Set RootDse = GetObject(LDAP://RootDSE)
    strdn = RootDse.Get("defaultNamingContext")
    strDomainName = "DOMAIN"
    strConfigurationNC = RootDse.Get("ConfigurationNamingContext")
    strExchangeOrg = FindAnyOrg(strConfigurationNC)
    strRUS = "CN=Recipient Update Service (" & strDomainName & "),CN=Recipient Update Services," & _
                "CN=Address Lists Container,CN=" & strExchangeOrg & ",CN=Microsoft Exchange,CN=Services," & _
             "CN=Configuration," & strdn
    Set objRUS = GetObject("LDAP://" & strRUS)
    objRUS.Put "msExchReplicateNow", True
    objRUS.SetInfo
    
    End Sub
    
    Function FindAnyOrg(strConfigurationNC)
    Set oConnection = CreateObject("ADODB.Connection")
       Set oCommand = CreateObject("ADODB.Command")
       Set oRecordSet = CreateObject("ADODB.RecordSet")
       Dim strQuery
    
       ' Open the Connection
       oConnection.Provider = "ADsDSOObject"
       oConnection.Open "ADs Provider"
       ' Build the query to find the private Exchange Organization
       strQuery = "<LDAP://" & strConfigurationNC & ">;(objectCategory=msExchOrganizationContainer);name,adspath;subtree"
       oCommand.ActiveConnection = oConnection
       oCommand.CommandText = strQuery
       Set oRecordSet = oCommand.Execute
    
       ' If we have an Organization then return the first one
       If Not oRecordSet.EOF Then
         oRecordSet.MoveFirst
         FindAnyOrg = CStr(oRecordSet.Fields("name").Value)
       Else
         FindAnyOrg = ""
       End If
    
       'Clean Up
       oRecordSet.Close
       oConnection.Close
       Set oRecordSet = Nothing
       Set oCommand = Nothing
       Set oConnection = Nothing
    End Function


    This script fires up the RUS so that users get stamped. Now you can combine this with any of our user creation scripts or Excel macros so that the users get stamped almost immediately. This is all very well but can be quite hefty in large Exchange servers with thousands of users. You can specify a waiting period using the WScript.Sleep command (the time specified is in milliseconds). Still if you add 4000 users you wouldn't want the RUS to run 4000 times, especially in a large environment.
    Instead we can remove the following line from our script:
    کد:
    oUser.Put "description", ID
    And add the FireRUS subroutine at the end of the script or simply run it separately. Now the script will look like this:
    کد:
    Sub CreateUsers()
    
    Dim Row As Integer
    Dim oMailbox As CDOEXM.IMailboxStore
    Dim oUser As IADsUser
    
    Set rootDSE = GetObject(LDAP://RootDSE)
    DomainContainer = rootDSE.Get("defaultNamingContext")
    Set oOU = GetObject(LDAP://OU=Test,DC=mycompany,DC=local)
    
    Row = 1
    
    Do Until Cells(Row, 1) = Empty
        gname = Trim(Cells(Row, 1).Value)
        sname = Trim(Cells(Row, 2).Value)
        ID = Cells(Row, 3).Value
        mailingaddress = Cells(Row, 4).Value
        city = Cells(Row, 5).Value
        postalcode = Cells(Row, 6).Value
        homephone = Cells(Row, 7).Value
        cellular = Cells(Row, 8).Value
        dept = Trim(Cells(Row, 9).Value)
    
        FullName = gname & " " & sname
    
        AliasCount = 2
        Alias = LCase(gname & Left(sname, AliasCount))
    
        Set conn = CreateObject("ADODB.Connection")
        conn.Provider = "ADSDSOObject"
        conn.Open "ADs Provider"
    
        ldapStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
    
        Set rs = conn.Execute(ldapStr)
    
        While rs.RecordCount > 0
          AliasCount = AliasCount + 1
          Alias = LCase(gname & Left(sname, AliasCount))
          ldapStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
          Set rs = conn.Execute(ldapStr)
    
       Wend
        ' Update User Record
        Set oUser = oOU.Create("user", "cn=" & FullName)
        oUser.Put "cn", FullName
        oUser.Put "SamAccountName", Alias
        oUser.Put "userPrincipalName", Alias & "@mycompany.local"
        oUser.Put "givenName", gname
        oUser.Put "sn", sname
        oUser.Put "streetaddress", mailingaddress
        oUser.Put "l", city 
        oUser.Put "postalCode" , CStr (postalcode)
    
        oUser.SetInfo
        oUser.GetInfo
    
        ' Enable Account
        oUser.AccountDisabled = False
        ' Set Pwd to be same as 123456
        oUser.SetPassword ("123456")
        'Account is not disabled
        oUser.AccountDisabled = False
        ' User must change password at next Logon
        oUser.Put "pwdLastSet", CLng(0)
    
        oUser.SetInfo 
    
        Set oMailbox = oUser
        MDBName = "Mailbox Store (EXCHANGE)"
        StorageGroup = "First Storage Group"
        Server = "Exchange"
        AdminGroup = "MyCompany"
        Organization = "MyCompany School of Arts"
        DomainDN = "DC=mycompany,DC=local"
    
        oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
                                       ",CN=" & StorageGroup & _
                                       ",CN=InformationStore" & _
                                       ",CN=" & Server & _
                                       ",CN=Servers" & _
                                       ",CN=" & AdminGroup & _
                                       ",CN=Administrative Groups" & _
                                       ",CN=" & Organization & _
                                       ",CN=Microsoft Exchange,CN=Services" & _
                                       ",CN=Configuration," & DomainDN
    
        oUser.SetInfo
    
        StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local"
        Set objGroup1 = GetObject(StrobjGroup1)
        objGroup1.Add (oUser.ADsPath)
    
        Set oUser = Nothing
        Row = Row + 1
    Loop
    
    FireRUS
    End Sub

    Notice that I added a few lines in the middle of the script to populate the user's address. This information will help the second macro locate the user.
    Adding the Attribute to the Users

    The second Macro reads the Excel cells as before, but instead of creating the user it searches for an existing one using the user's name and address. When it locates the user, it simply adds the ID number to the user object ExtensionAtttribute1 attribute.
    کد:
    Sub AddExtensionAttribute1()
    
    Dim Row As Integer
    Dim oUser As IADsUser
    
    Set RootDse = GetObject(LDAP://RootDSE)
    DomainContainer = RootDse.Get("defaultNamingContext")
    Set oOU = GetObject(LDAP://OU=Test,DC=domain,DC=local)
    
    Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "ADSDSOObject"
    conn.Open "ADs Provider"
    
    Row = 1
    
    Do Until Cells(Row, 1) = Empty
        gname = Trim(Cells(Row, 1).Value)
        sname = Trim(Cells(Row, 2).Value)
        ID = Cells(Row, 3).Value
        mailingaddress = Cells(Row, 4).Value
        city = Cells(Row, 5).Value
        postalcode = Cells(Row, 6).Value
        homephone = Cells(Row, 7).Value
        cellular = Cells(Row, 8).Value
        dept = Trim(Cells(Row, 9).Value)
    'Construct an LDAP query to Active Directory looking for users with the specified attributed,
    'first name, last name, address, etc.
        LDAPStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(givenName=" & gname & ")(sn=" & sname & ")(streetaddress=" & mailingaddress & ")(l=" & city & "));adspath;subtree"
    
             Set rs = conn.Execute(LDAPStr)
    'If there is more than one user found – and there supposed to be just one
            If rs.RecordCount > 0 Then
    'Populate the Exchange extension attribute no.1
                Set oUser = GetObject(rs.Fields(0).Value)
                oUser.Put "extensionAttribute1", ID
            oUser.SetInfo
            End If
    
        Set oUser = Nothing
        Set rs = Nothing
        Row = Row + 1
    Loop
    
    End Sub

    Synchronizing Users

    The script above is pretty simple yet we can use it as a base for a synchronization script. If we can locate a user, why not use this to our advantage and create a user if it is not found, or update a user's record?
    Let's add another user and change some information on our Excel sheet:

    Figure 2
    I added a new user and changed the zip code for another.
    Now all we need to do is combine the first and second script.
    کد:
    Sub SyncUsers()
    
    Dim Row As Integer
    Dim oMailbox As CDOEXM.IMailboxStore
    Dim oUser As IADsUser
    
    Set RootDse = GetObject(LDAP://RootDSE)
    DomainContainer = RootDse.Get("defaultNamingContext")
    Set oOU = GetObject(LDAP://OU=Test,DC=domain,DC=local)
    
    Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "ADSDSOObject"
    conn.Open "ADs Provider"
    
    Row = 1
    
    Do Until Cells(Row, 1) = Empty
    
        gname = Trim(Cells(Row, 1).Value)
        sname = Trim(Cells(Row, 2).Value)
        ID = Cells(Row, 3).Value
        mailingaddress = Cells(Row, 4).Value
        city = Cells(Row, 5).Value
        postalcode = Cells(Row, 6).Value
        homephone = Cells(Row, 7).Value
        cellular = Cells(Row, 8).Value
        dept = Trim(Cells(Row, 9).Value)
        LDAPStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(givenName=" & gname & ")(sn=" & sname & ")(streetaddress=" & mailingaddress & ")(l=" & city & "));adspath;subtree"
    
             Set rs = conn.Execute(LDAPStr)
        If rs.RecordCount > 0 Then
            Set oUser = GetObject(rs.Fields(0).Value)
           oUser.Put "streetaddress", mailingaddress
           oUser.Put "l", city
           oUser.Put "postalCode", CStr(postalcode)
           oUser.Put "extensionAttribute1", ID
           oUser.SetInfo
    
        Else
        'If Record Count is zero because no user is found    
           FullName = gname & " " & sname
           AliasCount = 2
           Alias = LCase(gname & Left(sname, AliasCount))
           Set conn = CreateObject("ADODB.Connection")
           conn.Provider = "ADSDSOObject"
           conn.Open "ADs Provider"
    
           LDAPStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
    
           Set rs = conn.Execute(LDAPStr)
    
           While rs.RecordCount > 0
             AliasCount = AliasCount + 1
             Alias = LCase(gname & Left(sname, AliasCount))
             LDAPStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
             Set rs = conn.Execute(LDAPStr)
    
           Wend
           ' Update User Record
           Set oUser = oOU.Create("user", "cn=" & FullName)
           oUser.Put "cn", FullName
           oUser.Put "SamAccountName", Alias
           oUser.Put "userPrincipalName", Alias & "@domain.local"
           oUser.Put "givenName", gname
           oUser.Put "sn", sname
    
            oUser.SetInfo
           oUser.GetInfo
    
           oUser.Put "streetaddress", mailingaddress
           oUser.Put "l", city
           oUser.Put "postalCode", CStr(postalcode)
           oUser.SetPassword "123456"
           oUser.AccountDisabled = False
    
           oUser.SetInfo
    
           Set oMailbox = oUser
           MDBName = "Mailbox Store (EXCHANGE)"
           StorageGroup = "First Storage Group"
           Server = "Exchange"
           AdminGroup = "AG"
           Organization = "Org"
           DomainDN = "DC=domain,DC=local"
    
           oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
                                          ",CN=" & StorageGroup & _
                                          ",CN=InformationStore" & _
                                          ",CN=" & Server & _
                                          ",CN=Servers" & _
                                          ",CN=" & AdminGroup & _
                                          ",CN=Administrative Groups" & _
                                          ",CN=" & Organization & _
                                          ",CN=Microsoft Exchange,CN=Services" & _
                                          ",CN=Configuration," & DomainDN
    
           oUser.SetInfo
    
           ' Enable Account
           oUser.AccountDisabled = False
           ' Set Pwd to be same as user name/alias
           oUser.SetPassword ("123456")
           ' User must change password at next Logon
           oUser.Put "pwdLastSet", CLng(0)
           oUser.SetInfo
           StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=domain,DC=local"
           Set objGroup1 = GetObject(StrobjGroup1)
           objGroup1.Add (oUser.ADsPath)
    
           Set oUser = Nothing
    
       End If
       Row = Row + 1
    Loop
    FireRUS
    Exit Sub
    
    End Sub

    The script goes through all the rows as before but if it finds a user that does not exist, it creates it. This does not perform full synchronization as ExtensionAttribue1 will only get updated during the second run of the script. However, if you run this script using a scheduler every few hours you will eventually get full synchronization.
    Conclusion

    We've established a mechanism that we can use to synchronize Active Directory using an Excel sheet. This opens up possibilities. You could have HR or secretaries edit this sheet without them having to learn how to use Active Directory Users and Computers and possibly without granting them any actual permissions. You can run all sorts of checks on an Excel sheet before entering the data into Active Directory. You can import information from another system into Excel and from there populate Active Directory. After all, most applications, even old ones can export to a CSV or Tab Separated text file which can be read by Excel.




کلمات کلیدی در جستجوها:

creating exchange users excel alias

create users exchange from excel

VBA CDOEXM.IMailboxStore reference

newuser.properties[extensionattribute1].value

enumerate vba user properties ouser.l

list users ldap query excel

extensionAttribute1 Iadsuser

add users to ad from excel

CDOEXM.IMailboxStore excel 2010

create exchange users from excel

password:@ %% theoffice.net 123456

objectCategory user exchange mailbox

extensionattribute 2 update from excel script

vba CDOEXM.IMailboxStore user defined type not found

برچسب برای این موضوع

مجوز های ارسال و ویرایش

  • شما نمی توانید موضوع جدید ارسال کنید
  • شما نمی توانید به پست ها پاسخ دهید
  • شما نمی توانید فایل پیوست ضمیمه کنید
  • شما نمی توانید پست های خود را ویرایش کنید
  •