Excel powershell Mass create of linked user accounts

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mandy_Author Commented:
Nobody an idea?  Thanks in advance

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

Mandy_Author Commented:
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

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mandy_Author Commented:
Great Work. Well done.Thank you so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.