asked on

Powershell read AD attributes and write to excel worksheet

The output from the code below should be written back to worksheet userlist in file test.xlsx
So far so good but 'm getting an error

Get-ADUser : Variable: "Name" found but $Name is not defined.
+ $SamName=Get-ADUser -Filter {Name -eq $Name} | select SamAccountName

I changed sth but nothing fixed that. I've 2 questions
1.How can i fix that?
2.How can i asked for more AD Attributes like Alias, company , get from Cell A1 and insert that in Cell $C2, &C3.....

$strPath="C:\Test.xlsx"
$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$false
$WorkBook=$objExcel.Workbooks.open($strPath)
$Worksheet=$WorkBook.Sheets.item("UserList")
$intRowMax=($Worksheet.UsedRange.Rows).count
$intRowMax = $intRowMax+4
$Columnnumber=1
$OutColNumber=2
for($intRow=4; $intRow -le $intRowMax; $intRow++){
	$Name=$Worksheet.Cells.item($intRow,$ColumnNumber).Value2
	$SamName=Get-ADUser -Filter {Name -eq $Name} | select SamAccountName
	$Worksheet.Cells.item($intRow,$OutColNumber)=$SamName.samaccountname
}
$WorkBook.SaveAs("C:\Test.xlsx")
$WorkBook.Close()
$objExcel.quit()

Open in new window

Patrick Bogers

Hi

What if you define $name firstly?

{param ($name) Get-ADUser -Filter {name -eq $name}}

Mandy_

ASKER

e.g i've 100 displaynames in excel from A1-A100. With this informations i will get the
alias from AD to Excel from B1-B100 and the SamAccountname from C1-C100.
The {param ($name) Get-ADUser -Filter {name -eq $name}} not getting the name
from excel or how you think i should use that?

Patrick Bogers

Hi

My suggestion was to try fixing your error firstly.
Do i understand that you want to input displaynames from excel sheet and then query the AD for the extra information?

Mandy_

ASKER

yes, thats the one

Mandy_

ASKER

could anybody could check this issue?

input displaynames from excel sheet and then query the AD for the extra information?

ASKER CERTIFIED SOLUTION

ThinkPaper

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Mandy_

ASKER

Thanks but i like to import it directly into excel cells and you can use only VBS.

the code below is almost perfect. The problem is :

Get-ADUser : Variable: "Name" found but $Name is not defined.

$strPath="C:\Test.xlsx"
$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$false
$WorkBook=$objExcel.Workbooks.open($strPath)
$Worksheet=$WorkBook.Sheets.item("UserList")
$intRowMax=($Worksheet.UsedRange.Rows).count
$intRowMax = $intRowMax+4
$Columnnumber=1
$OutColNumber=2
for($intRow=4; $intRow -le $intRowMax; $intRow++){
      $Name=$Worksheet.Cells.item($intRow,$ColumnNumber).Value2
      $SamName=Get-ADUser -Filter {Name -eq $Name} | select SamAccountName
      $Worksheet.Cells.item($intRow,$OutColNumber)=$SamName.samaccountname
}
$WorkBook.SaveAs("C:\Test.xlsx")
$WorkBook.Close()
$objExcel.quit()