Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-06-30
3
Medium Priority
?
928 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
ID: 39289583
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_
ID: 39289703
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 1600 total points
ID: 39289922
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

810 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