Vista - ADSI & Excel

Hi all.

I'm having a problem w/ the Powershell script. In this script, all i'm
trying to do is dump all the users out of my AD to an Excel
spreadsheet. Here's my attempt at the script:
--------------------
$a = New-Object -comobject Excel.Application
$a.visible = $False

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

#- Write header line -----------------------------
$c.Cells.Item(1,1) = "Common Name"
$c.Cells.Item(1,2) = "Given Name"
$c.Cells.Item(1,3) = "sAMAccountName"
$c.Cells.Item(1,4) = "E-mail Address"
$c.Cells.Item(1,5) = "Surname"
$c.Cells.Item(1,6) = "Description"

$d = $c.UsedRange
$d.Font.Bold = $True

$row = 2

$root = [ADSI]''
$searcher = New-Object DirectoryServices.DirectorySearcher $root
$searcher.Filter = "(&(objectcategory=person)(objectclass=user)(!
cn=*Calendar*))"
$users = $searcher.FindAll()

foreach ($user in $users)
{
$c.Cells.Item($row,1) = $user.properties.cn
$c.Cells.Item($row,2) = $user.properties.givenName
$c.Cells.Item($row,3) = $user.properties.samaccountname
$c.Cells.Item($row,4) = $user.properties.mail
$c.Cells.Item($row,5) = $user.properties.sn
$c.Cells.Item($row,6) = $user.properties.description

$row = $row + 1
}

$d.EntireColumn.AutoFit()
$b.SaveAs("C:\Download\Test.xls")
--------------------

The error i'm getting is:

ERROR: + $c.Cells.Item( <<<< $row,1) = $user.properties.cn
ERROR: Exception setting "Item": "Exception from HRESULT: 0x800A03EC"

This error re-occurs on columns 2 through 5 as well.

I've researched the error with little success. Can anyone assist me
with this error or suggest an alternate way to do what i'm
attempting?

Thanks.

jmuirh wrote:This would be much, much easier if you consider using the Quest AD
cmdlets and just use export-csv.

Example:
C:\PS>Get-QADuser jsmith -SerializeValues | export-csv user.csv

Quest AD cmdlets:
http://www.quest.com/activeroles-server/arms.aspx

Then you can use Excel to read in the CSV file.

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

You can also use the generic export-csv cmdlet to dump to a CSV file and
then open it in excel and do whatever you need.


--
Andy Schneider

Blog:
http://www.get-powershell.com




"Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com> wrote in message
news:%23loKSOabIHA.5984@xxxxxx

On Feb 12, 10:17 am, "Marco Shaw [MVP]"
<marco.shaw@_NO_SPAM_gmail.com> wrote:Marco,

I appreciate the assist and recommendations for downloading the third-
party cmdlets add-in, but ideally, i'm using this task or event as an
excuse to learn Powershell, as I am a relative newbie to the
environment and wanted to learn the "native" way of doing some things
first. ;-) no offense.

It's an educational endeavor. :-)

Again, many thanks.

Being difficult! ;-)

Good for you wanting to go at it the harder way. That particular error
brings up a lot of stuff, and it might be an Excel/COM issue. I tried
just this:

foreach ($user in $users)
{
$c.Cells.Item($row,2).value() = $user.properties.givenName
$row = $row + 1
}

And it worked fine.

See here for a similar issue:
http://forums.microsoft.com/MSDN/Sho...46670&SiteID=1

For the record, I tried with Excel 2007 (I don't seem to have SP1).

Marco