I know there is a lot of code in here. The objective of this is to have employees created in AD with as little interaction as possible . I am bringing it here with requests for suggestions of how to make it cleaner.
- HR creates new employees in access database
- Macro runs the query to gather specific depts/users that require AD.
- The macro exports the query as an .xlsx
- PShell converts the .xlsx to a csv
- PShell runs import-csv
- PShell renames column headers as per LDAP naming convention
- Imported password module runs,provides pwd
- New csv with pwd is exported so we can have the pwd to give to users.
- Run a new import-csv to new-aduser
#Creates new OU New-ADOrganizationalUnit #New AD Group - Not necessarily needed Add-Type -AssemblyName System.Web Import-Module ActiveDirectory #Password Randomizer Import-Module .\New-SWRandomPassword.psm1 # Create the Access application object $accessApp = new-object -com access.application #Open the database - use full path $accessApp.Application.OpenCurrentDatabase(#Access Database Location#) #Run Access query & run macro to export to .xlsx $accessApp.Application.DoCmd.OpenQuery("#AccessQueryName") $accessApp.Application.DoCmd.RunMacro("#MacroName") #Close database $accessApp.Application.CloseCurrentDatabase() #Close Access $accessApp.Quit() [System.Runtime.InteropServices.Marshal]::ReleaseComObject($accessApp) Remove-Variable accessApp #Converts .xlsx to csv Function ExportWSToCSV ($excelFileName, $csvLoc) { $excelFile = #ExcelFileLocation $E = New-Object -ComObject Excel.Application $E.Visible = $false $E.DisplayAlerts = $false $wb = $E.Workbooks.Open($excelFile) foreach ($ws in $wb.Worksheets) { $n = $ws.Name $ws.SaveAs($csvLoc + $n + ".csv", 6) } $E.Quit() } ExportWSToCSV -excelFileName "file" -csvLoc "#SaveLocation" #Imports CSV, renames field name as per AD requirements and exports to a new CSV with passwords included #Change Path to reflect the OU and DC for the company Import-CSV "#CSV-From-Above" | Select-Object Department,Title,EmployeeID, ` @{name='Name';expression={($_.'First Name'.substring(0)+$_.'Last Name').substring(0).toLower()}}, ` @{name='samAccountName';expression={($_.'First Name'.substring(0,1)+$_.'Last Name').substring(0).toLower()}}, ` #Change to respective UPN addressing @{name='userPrincipalName';expression={$_.'First Name'+'.'+$_.'Last Name'+'#domainname'}},` @{name='displayName';expression={$_.'First Name'+' '+$_.'Last Name'}}, ` @{name='givenName';expression={$_.'First Name'}}, ` @{name='surName';expression={$_.'Last Name'}},` @{name= 'mail'; expression={$_.'First Name'+'.'+$_.'Last Name'+'#EmailDomain'}},` @{name='path';expression={#'OU=container,DC=container,DC=local'}},` @{name='accountpassword';expression= {New-SWRandomPassword -MinPasswordLength 8 -MaxPasswordLength 10 -Count 1}}| Export-Csv #NewFileWithPasswords -NoTypeInformation #Imports the csv from last command and creates separate new users from each row in the csv $Users = Import-Csv #Import CSV with passwords ForEach ($User in $Users) { $Name = $User.name $GivenName = $User.givenName $Surname = $User.surName $SAM = $User.SAMAccountname $userPrincipalName = $User.userPrincipalName $Password = $User.accountpassword $Title=$User.Title $Department=$User.Department $City=$User.City $State=$User.State $Office=$User.Office $EmployeeID=$User.EmployeeID $Email=$User.name+"DomainName" #change to appropriate email address New-ADUser -Name $Name -GivenName $givenName -UserPrincipalName $userPrincipalName -Surname $surname -SamAccountName $SamAccountName -Email $Email -Department $Department -EmployeeID $EmployeeID -Title $Title -Enabled $True -AccountPassword $(ConvertTo-SecureString -string $Password -AsPlainText -force) -PasswordNeverExpires $True -Path 'OU=container,DC=container,DC=local'-PassThru}