Solved

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

Posted on 2013-06-30
3
897 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 400 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

914 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

18 Experts available now in Live!

Get 1:1 Help Now