Powershell - Get AD Attribute "SAMACCOUNT" and write it to Excel Worksheet and cell

Hi,

i like to get Ad-Attribute "SAMaccountname" from Active Directory and write it to Worksheet "test" in cell Col B4-B37 down.

The name to search in AD for the "SAMACCOUNT" i will get from excel A4-A37

e.g.
displayname
Cell A4,A5,A6,A7
User1, Ted
User2, Fred
User3, Mike
User4, Gene


get-aduser -Filter {DisplayName -eq "$A4$"}).SamAccountName
get-aduser -Filter {DisplayName -eq "$A5$"}).SamAccountName
get-aduser -Filter {DisplayName -eq "$A6$"}).SamAccountName
get-aduser -Filter {DisplayName -eq "$A7$"}).SamAccountName

Now writeback the result to Cell B4-B37 in excel
LVL 2
Mandy_Asked:
Who is Participating?
 
SreRajCommented:
On line number 15 we change the code to '$WorkBook.SaveAs("C:\Test.xlsx")'. This will give a warning which says the file already exists on the location, do you want to overwrite it. We need to select Yes and the script will overwrite existing file.

Please find the modified code as follows.

$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

0
 
SreRajCommented:
Hi,

Please try the following script. It takes input from C:\Test.xlsx and writes output to C:\TestResult.xlsx.

$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:\TestResult.xlsx")
$WorkBook.Close()
$objExcel.quit()

Open in new window

0
 
Mandy_Author Commented:
thats for sure a good beginning.  And when i want to read and write to same document
e.g. test.xlsx from Worksheet "userlist" the cells A4-A20 and write back the result
to same worksheet cells e4-E20?

appreciate for your help
0
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.

All Courses

From novice to tech pro — start learning today.