Solved

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

Posted on 2013-06-30
3
895 Views
Last Modified: 2013-07-01
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
0
Comment
Question by:Mandy_
  • 2
3 Comments
 
LVL 12

Expert Comment

by:SreRaj
Comment Utility
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
 
LVL 2

Author Comment

by:Mandy_
Comment Utility
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
 
LVL 12

Accepted Solution

by:
SreRaj earned 400 total points
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Local Continuous Replication is a cost effective and quick way of backing up Exchange server data. The following article describes the steps required to configure Local Continuous Replication. Also, the article tells you how to restore from a backup…
This article will help you understand what HashTables are and how to use them in PowerShell.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
how to add IIS SMTP to handle application/Scanner relays into office 365.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now