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


Excel powershell Mass create of linked user accounts

Posted on 2014-08-09
Medium Priority
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_
  • 3
  • 2

Author Comment

ID: 40253903
Nobody an idea?  Thanks in advance

LVL 71

Expert Comment

ID: 40266093
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)
G2:  test.com
H2:  ="Disable-ADAccount -Identity "&B2&";Start-Sleep -s 20;Enable-Mailbox -Identity "&B2&" -Alias "&C2&" -LinkedMasterAccount 'ad\"&C2&"' -LinkedDomainController 'SWRQLHADDC05.ad.test.com' -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&"@test.com' -Identity "&B2&""

Open in new window

Though, the last part (setting smtp:) is wrong. E2 (former A5) contains the complete email, and you append "@test.com" again. H2 should hence be:
="Disable-ADAccount -Identity "&B2&";Start-Sleep -s 20;Enable-Mailbox -Identity "&B2&" -Alias "&C2&" -LinkedMasterAccount 'ad\"&C2&"' -LinkedDomainController 'SWRQLHADDC05.ad.test.com' -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


Author Comment

ID: 40266222
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 'SWRQLHADDC05.ad.test.com' -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 71

Accepted Solution

Qlemo earned 2000 total points
ID: 40266248
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.

Author Closing Comment

ID: 40266271
Great Work. Well done.Thank you so much

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

873 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