[Powershell] Create AD User Script

# ERROR REPORTING ALL
Set-StrictMode -Version latest

#----------------------------------------------------------
# LOAD ASSEMBLIES AND MODULES
#----------------------------------------------------------
Try
{
  Import-Module ActiveDirectory -ErrorAction Stop
}
Catch
{
  Write-Host "[ERROR]`t ActiveDirectory Module couldn't be loaded. Script will stop!"
  Exit 1
}

#----------------------------------------------------------
#STATIC VARIABLES
#----------------------------------------------------------
$path     = Split-Path -parent $MyInvocation.MyCommand.Definition
$newpath  = $path + "\create_user.xlsx"
$FileName = $newpath
$log      = $path + "\create_ad_users.log"
$date     = Get-Date
$addn     = (Get-ADDomain).DistinguishedName
$dnsroot  = (Get-ADDomain).DNSRoot
$i        = 1


#----------------------------------------------------------
#Import-Excel
#----------------------------------------------------------
Function Import-FromExcel
{
Param(
[Parameter(Mandatory=$true)]
[String]$WorkbookPath
)

# Constant values
[int]$xlToRight = -4161; [int]$xlToLeft = -4159; [int]$xlUp = -4162; [int]$xlDown = -4121;

If (($objExcel = New-Object -ComObject Excel.Application)) { Write-Verbose "Created Excel application object" }
Else { Write-Host "Unable to create Excel object on this computer. Check if you have Excel installed." -ForegroundColor Red; Return $Error[0].CategoryInfo.Category; }

If(Test-Path -Path $WorkbookPath)
{
If(($objWorkbook = $objExcel.Workbooks.Open($WorkbookPath))) { Write-Verbose "Opened $WorkbookPath successfully" }
Else { Write-Host "Unable to open $WorkbookPath." -ForegroundColor Red; Return $Error[0].Exception.Message }
}
Else { Return "Workbook not found $WorkbookPath!" }

$objSheet = $objWorkbook.Sheets.Item(1)
$TotalColumnsAddress = ($objSheet.Cells(1,$objSheet.Columns.Count).EntireColumn.Address($false, $false)).ToString().Split(":")[0]
$ColumnCount = $objSheet.Range("$($TotalColumnsAddress)1").End($xlToLeft).Column
$RowCount = $objSheet.Range("A$($objSheet.Rows.Count)").End($xlUp).Row
$LastColumnAddress = ($objSheet.Range("$($TotalColumnsAddress)1").End($xlToLeft).EntireColumn.Address($false,$false)).ToString().Split(":")[0]
Write-Verbose "Total column address limit is $TotalColumnsAddress`n Whereas Last column in data is $LastColumnAddress"

If ($ColumnCount -ge 1 -and $RowCount -ge 2) { Write-Verbose "Found $ColumnCount column and $RowCount rows of data in workbook."}
Else
{
Write-Host "No records found for processing. There should be at least one record/row apart from the header row in Workbook" -ForegroundColor Red
Return "No records found in input workbook file"
}

Write-Verbose "Converting Excel data into an object."
$ColHeaders = @()
$ColHeaders = $objSheet.Range("A1:$($objSheet.Range("$($TotalColumnsAddress)1").End($xlToLeft).Address($false,$false))").value2
# Replace blank column headers with Column'n'
Write-Verbose "Generating object headers from workbook"
For($i=1; $i -le $ColHeaders.Count; $i++)
{
if($ColHeaders[1,$i] -eq "" -or $ColHeaders[1,$i] -eq $null)
{
Write-Verbose "Column $i was found empty hence assigning column name as Column$i"
$ColHeaders[1,$i] = "Column$i"
}
}

$objOutData = @()

$DataRange = $objSheet.Range("A2:$($LastColumnAddress)$RowCount").Rows
Write-Verbose "Started processing rows/records from workbook"
For($curRow=2; $curRow -le $RowCount; $curRow++)
{
Write-Verbose "Working on row number $curRow."
$rowData = $DataRange.Rows | Where-Object { $_.Row -eq $curRow } | %{$_.Value2}
# Blank object for properties
$objRecord = New-Object -TypeName PSObject
For($curColumn = 1; $curColumn -le $ColumnCount; $curColumn++)
{
# Adjusting the number format for column
If($objSheet.Cells($curRow, $curColumn).NumberFormat -eq "General")
{
$ValueData = $rowData[$curColumn-1]
}
Else
{
If(($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch "d" -or
($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch "M" -or
($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch "yy")
{
If(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "h:" -and
($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "mm")
{
Write-Verbose "Number format found: $($objSheet.Cells($curRow, $curColumn).NumberFormat)"
$ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString('ddd, dd-MMM-yyyy hh:mm:ss tt')
}
Else
{
$ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString('dd-MMM-yyyy')
}
}
ElseIf(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "h:" -or
($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "mm" -or
($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "ss")
{
$ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString('hh:mm:ss')
}
ElseIf(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "0" -or
($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "0.0")
{
$value = 0
If([double]::TryParse($rowData[$curColumn-1], [ref]$value)) { $ValueData = $value }
Else { $ValueData = $rowData[$curColumn-1] }
}
Else
{ $ValueData = $rowData[$curColumn-1] }
}
$objRecord | Add-Member -MemberType NoteProperty -Name $ColHeaders[1,$curColumn] -Value $ValueData -Force
}
$objOutData += $objRecord
}

# Quit Excel and release all resources.
$objWorkbook.Close($false)
$objExcel.Quit()
Write-Verbose "Successfully processed and closed Excel application"

# Supress errors temporarily
$oldErrorActionPreference = $ErrorActionPreference
$ErrorActionPreference = 'SilentlyContinue'

Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($RowCount) } while($comReleaser -gt -1) } Catch {}
Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ColumnCount) } while($comReleaser -gt -1) } Catch {}
Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($DataRange) } while($comReleaser -gt -1) } Catch {}
Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objSheet) } while($comReleaser -gt -1) } Catch {}
Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorkbook) } while($comReleaser -gt -1) } Catch {}
Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel) } while($comReleaser -gt -1) } Catch {}
Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objRecord) } while($comReleaser -gt -1) } Catch {}
Write-Verbose "Released all the Com objects."
$ErrorActionPreference = $oldErrorActionPreference

Return $objOutData
}


#----------------------------------------------------------


#----------------------------------------------------------
#START FUNCTIONS
#----------------------------------------------------------
Function Start-Commands
{
  Create-Users
}

Function Create-Users
{
  "Processing started (on " + $date + "): " | Out-File $log -append
  "--------------------------------------------" | Out-File $log -append
  Import-FromExcel | ForEach-Object
    If (($_.Implement.ToLower()) -eq "yes")
    {
      If (($_.GivenName -eq "") -Or ($_.LastName -eq ""))
      {
        Write-Host "[ERROR]`t Please provide valid GivenName, LastName and Initials. Processing skipped for line $($i)`r`n"
        "[ERROR]`t Please provide valid GivenName, LastName and Initials. Processing skipped for line $($i)`r`n" | Out-File $log -append
      }
      Else
      {
        # Set the target OU
        $location = $_.TargetOU + ",$($addn)"

        # Set the Enabled and PasswordNeverExpires properties
        If (($_.Enabled.ToLower()) -eq "true") { $enabled = $True } Else { $enabled = $False }
        If (($_.PasswordNeverExpires.ToLower()) -eq "true") { $expires = $True } Else { $expires = $False }

        # A check for the country, because those were full names and need
        # to be land codes in order for AD to accept them. I used Netherlands
        # as example
        If($_.Country -eq "Deutschland")
        {
          $_.Country = "DE"
        }
        Else
        {
          $_.Country = "EN"
        }
        # Replace dots / points (.) in names, because AD will error when a
        # name ends with a dot (and it looks cleaner as well)
        $replace = $_.Lastname.Replace(".","")
        If($replace.length -lt 4)
        {
          $lastname = $replace
        }
        Else
        {
          $lastname = $replace.substring(0,4)
        }
        # Create sAMAccountName according to this 'naming convention':
        # <FirstLetterInitials><FirstFourLettersLastName> for example
        # htehp
        $sam = $_.GivenName.substring(0,1).ToLower() + $lastname.ToLower()
        Try   { $exists = Get-ADUser -LDAPFilter "(sAMAccountName=$sam)" }
        Catch { }
        If(!$exists)
        {
          # Set all variables according to the table names in the Excel
          # sheet / import CSV. The names can differ in every project, but
          # if the names change, make sure to change it below as well.
          $setpass = ConvertTo-SecureString -AsPlainText $_.Password -force

          Try
          {
            Write-Host "[INFO]`t Creating user : $($sam)"
            "[INFO]`t Creating user : $($sam)" | Out-File $log -append
            New-ADUser $sam -GivenName $_.GivenName -Initials $_.Initials `
            -Surname $_.LastName -DisplayName ($_.LastName + "," + $_.Initials + " " + $_.GivenName) `
            -Office $_.OfficeName -Description $_.Description -EmailAddress $_.Mail `
            -StreetAddress $_.StreetAddress -City $_.City -State $_.State `
            -PostalCode $_.PostalCode -Country $_.Country -UserPrincipalName ($sam + "@" + $dnsroot) `
            -Company $_.Company -Department $_.Department -EmployeeID $_.EmployeeID `
            -Title $_.Title -OfficePhone $_.Phone -AccountPassword $setpass -Manager $_.Manager `
            -profilePath $_.ProfilePath -scriptPath $_.ScriptPath -homeDirectory $_.HomeDirectory `
            -homeDrive $_.homeDrive -Enabled $enabled -PasswordNeverExpires $expires
            Write-Host "[INFO]`t Created new user : $($sam)"
            "[INFO]`t Created new user : $($sam)" | Out-File $log -append

            $dn = (Get-ADUser $sam).DistinguishedName
            # Set an ExtensionAttribute
            #If ($_.ExtensionAttribute1 -ne "" -And $_.ExtensionAttribute1 -ne $Null)
            #{
            #  $ext = [ADSI]"LDAP://$dn"
            #  $ext.Put("extensionAttribute1", $_.ExtensionAttribute1)
            #  Try   { $ext.SetInfo() }
            #  Catch { Write-Host "[ERROR]`t Couldn't set the Extension Attribute : $($_.Exception.Message)" }
            #}

            # Set ProxyAdresses
            Try { $dn | Set-ADUser -Add @{proxyAddresses = ($_.ProxyAddresses -split ";")} -ErrorAction Stop }
            Catch { Write-Host "[ERROR]`t Couldn't set the ProxyAddresses Attributes : $($_.Exception.Message)" }

            # Move the user to the OU ($location) you set above. If you don't
            # want to move the user(s) and just create them in the global Users
            # OU, comment the string below
            If ([adsi]::Exists("LDAP://$($location)"))
            {
              Move-ADObject -Identity $dn -TargetPath $location
              Write-Host "[INFO]`t User $sam moved to target OU : $($location)"
              "[INFO]`t User $sam moved to target OU : $($location)" | Out-File $log -append
            }
            Else
            {
              Write-Host "[ERROR]`t Targeted OU couldn't be found. Newly created user wasn't moved!"
              "[ERROR]`t Targeted OU couldn't be found. Newly created user wasn't moved!" | Out-File $log -append
            }

            # Rename the object to a good looking name (otherwise you see
            # the 'ugly' shortened sAMAccountNames as a name in AD. This
            # can't be set right away (as sAMAccountName) due to the 20
            # character restriction
            $newdn = (Get-ADUser $sam).DistinguishedName
            Rename-ADObject -Identity $newdn -NewName ($_.GivenName + " " + $_.LastName)
            Write-Host "[INFO]`t Renamed $($sam) to $($_.GivenName) $($_.LastName)`r`n"
            "[INFO]`t Renamed $($sam) to $($_.GivenName) $($_.LastName)`r`n" | Out-File $log -append
          }
          Catch
          {
            Write-Host "[ERROR]`t Oops, something went wrong: $($_.Exception.Message)`r`n"
          }
        }
        Else
        {
          Write-Host "[SKIP]`t User $($sam) ($($_.GivenName) $($_.LastName)) already exists or returned an error!`r`n"
          "[SKIP]`t User $($sam) ($($_.GivenName) $($_.LastName)) already exists or returned an error!" | Out-File $log -append
        }
      }
    }
    Else
    {
      Write-Host "[SKIP]`t User ($($_.GivenName) $($_.LastName)) will be skipped for processing!`r`n"
      "[SKIP]`t User ($($_.GivenName) $($_.LastName)) will be skipped for processing!" | Out-File $log -append
    }
    $i++
  }
  "--------------------------------------------" + "`r`n" | Out-File $log -append
}

Write-Host "STARTED SCRIPT`r`n"
Write-Host "STOPPED SCRIPT"