Post date: Nov 22, 2010 2:49:38 PM
I wrote a little script to extract phonenumbers out of our Active Directory and put them in an Excel document.
I reused most of the scripting i found on the internet, and just adjusted some to fit my own purposes.
The handy part of the script is in the tail where i want to give a cel-format to prevent excel from cutting the 0 (zero) in front of the phone number.
Also the outcome is sorted and the cellwidth is adjusted.
BTW: If you have any questions about scripting in Powershell, i might be of any help.
Here's the script:
cls
$strPath="c:\xml\test999.xls"
$a = new-object -comobject excel.application
$a.Visible = $true
if (Test-Path $strPath)
{
#Open the document
$b = $a.WorkBooks.Open($strPath)
$c = $b.Worksheets.Item(1) }
else
{ # Create It $b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1) }
$introw = 1
$users = Get-QADUser
foreach ($user in $users){
if ($user.HomePhone -gt '') {
$c.cells.item($introw, 1) = $user.LogonName
$c.cells.item($introw, 2) = $user.HomePhone
$introw++} }
$objRange = $c.UsedRange
$objRange2 = $a.Range("A1")
[void] $objRange.Sort($objRange2)
$objRange.NumberFormat = "0#########"
$objRange.EntireColumn.AutoFit() |out-null