How to return a Powershell Object Property Value to an excel Cell

Question

So I am running a Powershell script that will return the ALL the Users and their Access Permissions for all Shared Folders with in a Specified Folder. My problem is, I am trying to get Powershell to dump the info directly into an open excel file. So far the script runs outputs to a CSV file with the correct info, but when when I try to get it to dump into the excel it doesn't work. his is because I can not figure out how to set the cell value to the Object property value.

Here is the Code

#Get Folder Permissions

$xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
$targetWBpath = 'C:\<Path>\test_dump.xlsx'
$targetWbName = Split-Path $targetWbpath -Leaf
$targetWB = $xl.Workbooks.Item($targetWBName)
$targetWS = $targetWB.Sheets.Item('Test_1')
$cells = $targetWS.Cells
$row = 1
$col = 1

"User","Folder","Access" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

$row = 2

Get-childitem "<Path to Target Folder>" -recurse | where{$_.psiscontainer} |

Get-Acl | % {

$path = $_.Path

$_.Access | % {

New-Object PSObject -Property @{

Folder = $path.Replace("Microsoft.PowerShell.Core\FileSystem::","")

Access = $_.FileSystemRights

User = $_.IdentityReference

} 

$col = 1
$cells.item($row,$col) = User <How do I get the Property Value Here for the User property>
$col++
$cells.item($row,$col) = Folder <How do I get the Property Value Here for the Folder property>
$col++
$cells.item($row,$col) = Access <How do I get the Property Value Here for the Access property>
$col++
$row++
}
}

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

It would be nice if I could figure out how to do a Foreach loop to retrieve the property values , but I couldn't figure that out either. FYI this is my first Powershell script so I apologize if this is a not so challenging question. Overall Goal is to call this from a VBA script and have the output placed in the Workbook the script is running from.

Thank you for your help, JD

score 0 · Accepted Answer · answered Mar 21 '14 at 21:26

0

I haven't worked much with the Excel interop, but as far as accessing the properties, you need to assign the object to a variable before you can refer to its properties:

$aclObj = New-Object PSObject -Property @{
  Folder = $path.Replace("Microsoft.PowerShell.Core\FileSystem::","")
  Access = $_.FileSystemRights
  User = $_.IdentityReference
}

Then, accessing them is simple:

$col = 1
$cells.item($row,$col) = $aclObj.User
$col++
$cells.item($row,$col) = $aclObj.Folder
$col++
$cells.item($row,$col) = $aclObj.Access
$col++

Alternately, do you really need to create an object? If this is all you're doing with it, why not just assign the values you want directly to Excel?

$_.Access | % {
  $col = 1
  $cells.item($row,$col) = $_.IdentityReference
  $col++
  $cells.item($row,$col) = $path.Replace("Microsoft.PowerShell.Core\FileSystem::","")
  $col++
  $cells.item($row,$col) = $_.FileSystemRights
  $row++
}

answered Mar 21 '14 at 21:26

KevinD

3,023
2
22
26

KevinD, Thank you for your response.I created the AclObj variable to access the properties. However, When I Checked the User Property `Write-Host "User : $AclObj.User"` all properties for the AclObj were displayed and then when it dumps into the excel file all of the properties are placed in the cell. – Jim D Mar 24 '14 at 16:13
I got it figured out. I created a variable for the Object like you said. But for some reason I had to create a variable `$User = $AclObj.User` in order to get the exact property into the cell. Cell code was `$Cells.item($row,$col) = "$User"` I also had to put the User variable in quotations to get it to work. – Jim D Mar 24 '14 at 16:30
The reason why is due to how PowerShell adds variables to strings. You'd have to use Write-Host "User : $($AclObj.User)" if you don't want an extra variable. See this (the first question I ever asked here!) for more info: http://stackoverflow.com/questions/13615676/what-does-variablename-mean-in-powershell – KevinD Mar 24 '14 at 16:34
THANK YOU. That is a very helpful tip to keep in mind. – Jim D Mar 24 '14 at 20:15
Why is it that the permissions returned are not the same as the Permissions that one sees when using the Security tab from the folders properties display? And what does the Synchronize permission do? – Jim D Mar 25 '14 at 03:23

How to return a Powershell Object Property Value to an excel Cell

1 Answers1