Excel powershell Mass create of linked user accounts

Posted on 2014-08-09
Last Modified: 2014-08-17
hi experts,

in the attachment you can find an example how to create linked master accounts from excel with powershell.

Any idea to change that to handle mass user like 30 and maybe only fill the first field "displayname"  and query
the other fields (samaccountname, mailnickname) directly from AD in excel??
The generated powershell lines should be copy and paste to powershell window manually.
appreciate for your help

Question by:Mandy_
    LVL 2

    Author Comment

    Nobody an idea?  Thanks in advance

    LVL 67

    Expert Comment

    We can leave the PowerShell topic aside; this is completely to be solved inside Excel, because it is all about reading LDAP properties from AD to enter info in Excel. The PS code is auto-generated by a formula.

    The VBA code would work exactly as you request if you add the additional calculations in columns D onwards, with the PS "script" referring to them. Important: don't use column headers in row 1 for those additional columns. That way the VBA script does not think that columns are LDAP attributes to read into Excel.

    In detail, put the following formulas in the respective cells, and drag them down:
    D2:  =WECHSELN(WECHSELN(KLEIN(RECHTS(A2;(LÄNGE(A2))-(SUCHEN(",";A2))-1)&"."&LINKS(A2;(SUCHEN(",";A2)-1))&"@"&G2);" ";"");"/";"")
    E2:  =KLEIN(C2) & "@" & G2
    F2:  ="X400:C=DE;A=ATTMAIL;P=LH;O=SWISS;S="&GROSS(LINKS(A2;SUCHEN(",";A2)-1))&";G="&RECHTS(A2;(LÄNGE(A2))-(SUCHEN(",";A2))-1)
    H2:  ="Disable-ADAccount -Identity "&B2&";Start-Sleep -s 20;Enable-Mailbox -Identity "&B2&" -Alias "&C2&" -LinkedMasterAccount 'ad\"&C2&"' -LinkedDomainController '' -LinkedCredential $creds;Start-sleep -s 15;Set-Mailbox -Alias "&C2&" -CustomAttribute4 'LA' -CustomAttribute10 'LA' -EmailAddressPolicyEnabled $false -EmailAddresses 'smtp:"&B2&"@test.DE','"&F2&"','SMTP:"&D2&"','smtp:"&E2&"' -Identity "&B2&""

    Open in new window

    Though, the last part (setting smtp:) is wrong. E2 (former A5) contains the complete email, and you append "" again. H2 should hence be:
    ="Disable-ADAccount -Identity "&B2&";Start-Sleep -s 20;Enable-Mailbox -Identity "&B2&" -Alias "&C2&" -LinkedMasterAccount 'ad\"&C2&"' -LinkedDomainController '' -LinkedCredential $creds;Start-sleep -s 15;Set-Mailbox -Alias "&C2&" -CustomAttribute4 'LA' -CustomAttribute10 'LA' -EmailAddressPolicyEnabled $false -EmailAddresses 'smtp:"&B2&"@test.DE','"&F2&"','SMTP:"&D2&"','smtp:"&E2&"' -Identity "&B2&""

    Open in new window

    LVL 2

    Author Comment

    Hi qlemo,

    thanks so much.  Unfortunately the end the fields are not resolve:
    Pls see my attached document. appreciate for your help

    '="X400:C=DE;A=ATTMAIL;P=LH;O=SWISS;S="&GROSS(LINKS(A2;SUCHEN(",";A2)-1))&";G="&RECHTS(A2;(LÄNGE(A2))-(SUCHEN(",";A2))-1)','SMTP:=WECHSELN(WECHSELN(KLEIN(RECHTS(A2;(LÄNGE(A2))-(SUCHEN(",";A2))-1)&"."&LINKS(A2;(SUCHEN(",";A2)-1))&"@"&G2);" ";"");"/";"")','smtp: =KLEIN(C2) & "@" & G2' -Identity D555555

    Disable-ADAccount -Identity D555555;Start-Sleep -s 20;Enable-Mailbox -Identity D555555 -Alias test -LinkedMasterAccount 'ad\test' -LinkedDomainController '' -LinkedCredential $creds;Start-sleep -s 15;Set-Mailbox -Alias test -CustomAttribute4 'LA' -CustomAttribute10 'LA' -EmailAddressPolicyEnabled $false -EmailAddresses 'smtp:D555555@test.DE','="X400:C=DE;A=ATTMAIL;P=LH;O=SWISS;S="&GROSS(LINKS(A2;SUCHEN(",";A2)-1))&";G="&RECHTS(A2;(LÄNGE(A2))-(SUCHEN(",";A2))-1)','SMTP:=WECHSELN(WECHSELN(KLEIN(RECHTS(A2;(LÄNGE(A2))-(SUCHEN(",";A2))-1)&"."&LINKS(A2;(SUCHEN(",";A2)-1))&"@"&G2);" ";"");"/";"")','smtp: =KLEIN(C2) & "@" & G2' -Identity D555555

    Open in new window

    LVL 67

    Accepted Solution

    The formulas have not been evaluated, but stored as text. Don't know why exactly, as just pressing return on all columns helped with exception of E, where I had to remove the leading space. Correct XLSM attached.
    LVL 2

    Author Closing Comment

    Great Work. Well done.Thank you so much

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Set OWA language and time zone in Exchange for individuals, all users or per database.
    Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now