CODEWITHSUNDEEP
excelpowershell
Kevin Bates
Kevin Bates

Reputation: 3

Powershell script for writing in an excelsheet

Writing a powershell script here to fill some data from our AD to an excelsheet. I am struggling with one issue though..

I use this line to read out the stuff I want in the variable $accounts:

$accounts = Get-ADUser -Filter * -SearchBase "OU=technicalaccounts,OU=ztechnical,DC=XXX,DC=XXX,DC=XX" -Properties memberof, passwordlastset, description, enabled

Then the filling up the excelsheet follows in a Foreach

$Sheet.Cells.Item($row,1).FormulaLocal = $value.name
$Sheet.Cells.Item($row,2).FormulaLocal = $value.description
$Sheet.Cells.Item($row,3).FormulaLocal = $value.passwordlastset
$Sheet.Cells.Item($row,4).FormulaLocal = $value.enabled
$Sheet.Cells.Item($row,5).FormulaLocal = $value.memberof

The problem occurs when it's doing this one

$Sheet.Cells.Item($row,5).FormulaLocal = $value.memberof

The error says this:

Ausnahme beim Festlegen von "FormulaLocal": "Ausnahme von HRESULT: 0x800A03EC" In \Get-TechUser.ps1:57 Zeichen:9 + $Sheet.Cells.Item($row,5).FormulaLocal = $value.memberof + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException + FullyQualifiedErrorId : CatchFromBaseAdapterSetValueTI

Is this an array in an array kinda thing?

Well anyway thanks for any advice :)

Upvotes: 0

Views: 272

Answers (1)

Martin
Martin

Reputation: 1963

The memberof property is a collection (ADPropertyValueCollection) of the Group Memberships. You can i.e. use the join Operator:

$Sheet.Cells.Item($row,5).FormulaLocal = $value.memberof -join ";"

Upvotes: 1

Related Questions