Here's powershell script :
copy-item "\\xxx.xlsx" -destination "c:\liste\liste.xlsx" -force
$filepath = "c:\liste\liste.xlsx"
$Excel = New-Object -ComObject excel.application
$Workbook = $excel.Workbooks.open($filepath)
$Worksheet = $Workbook.WorkSheets.item("Feuil1")
$Worksheet.activate()
$line = 4
$col = 1
$MobilePhone=''
$DECTPhone=''
$FixePhone=''
$DN=''
while ($worksheet.Cells.Item($line,1).Value())
{
if ($worksheet.Cells.Item($line,4).Value() -like "*06-??-??-??-??*" -or $worksheet.Cells.Item($line,4).Value() -like "*07-??-??-??-??*" -or $worksheet.Cells.Item($line,4).Value() -like "*???-???-????*" -or $worksheet.Cells.Item($line,4).Value() -like "*????-???-????-????*")
{
$MobilePhone = $worksheet.Cells.Item($line,4).Value()
}
if ($worksheet.Cells.Item($line,5).Value() -like "*[0-9][0-9][0-9][0-9]*")
{
$DECTPhone = $worksheet.Cells.Item($line,5).Value()
}
if ($worksheet.Cells.Item($line,6).Value() -like "*01-??-??-??-??*" -or $worksheet.Cells.Item($line,6).Value() -like "*02-??-??-??-??*" -or $worksheet.Cells.Item($line,6).Value() -like "*03-??-??-??-??*" -or $worksheet.Cells.Item($line,6).Value() -like "*04-??-??-??-??*" -or $worksheet.Cells.Item($line,6).Value() -like "*05-??-??-??-??*" -or $worksheet.Cells.Item($line,6).Value() -like "*09-??-??-??-??*" -or $worksheet.Cells.Item($line,6).Value() -like "*????-???-????-????*")
{
$FixePhone = $worksheet.Cells.Item($line,6).Value()
}
$NOM = $worksheet.Cells.Item($line,1).Value()
$PRENOM = $worksheet.Cells.Item($line,2).Value()
if ((Get-ADObject -LDAPFilter "(&(GivenName=$PRENOM)(Sn=$NOM))" | ft -HideTableHeaders DistinguishedName | Out-String).Trim())
{
$DN = (Get-ADObject -LDAPFilter "(&(GivenName=$PRENOM)(Sn=$NOM))" | ft -HideTableHeaders DistinguishedName | Out-String).Trim()
#Dans le cas ou l'utilisateur à plusieurs comptes, on place $DN dans un foreach
foreach ( $a in $($DN -split "`r`n") )
{
if ( $MobilePhone -eq '' )
{
Set-ADObject -Identity $a -Clear mobile
}
else
{
if ($MobilePhone -like "*0?-??-??-??-??*")
{
Set-ADObject -Identity $a -Replace @{mobile=$MobilePhone}
}
}
if ( $DECTPhone -eq '' )
{
Set-ADObject -Identity $a -Clear otherTelephone
}
else
{
Set-ADObject -Identity $a -Replace @{otherTelephone=$DECTPhone}
}
if ( $FixePhone -eq '' )
{
Set-ADObject -Identity $a -Clear telephoneNumber
}
else
{
if ($FixePhone -like "*0?-??-??-??-??*")
{
Set-ADObject -Identity $a -Replace @{telephoneNumber=$FixePhone}
}
}
}
}
$MobilePhone=''
$DECTPhone=''
$FixePhone=''
$DN=''
$line++
}
$workbook.Close()
$excel.Quit()
Script is working on Windows 2008 R2 but not on Windows server 2019.
Set-ADObject : Type non valide « System.Double ». Nom du paramètre : otherTelephone Au caractère Ligne:63 : 5 + ... Set-ADObject -Identity $a -Replace @{otherTelephone=$DECT ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument : (CN=XXXX\, Xx...T,DC=XXX,DC=XXX:ADObject) [Set-ADObject], ArgumentException + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Commands.SetADObject
When i initialise $NOM and $PRENOM and $DN with get-adobject, set-adobject command works without system.double error. Thanks for help.