?
Solved

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

Posted on 2013-06-30
3
Medium Priority
?
919 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses

777 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