Calling Powershell from VBA

PowerShell Help
1

Hi - I am calling a Powershell command from Excel VBA and want to get the output back into Excel so I can write it to a sheet.
I think the issue I’m having is that the command contains “foreach-object” and that (possibly because there are multiple records) the data does not get written back to stdout.
The command works fine if I run it directly in Powershell but as soon as I attempt to write the results to any file/output then I either get no data or what I assume is the return codes for the command rather than the data.
The Powershell command is just getting the ACL for a folder.

Can anyone help?
My VBA Code looks like this:

cmd1 = "PowerShell -executionpolicy remotesigned -Command "
cmd2 = "get-acl '" & sFolderName & "' "
cmd3 = " | foreach-object %{ $_.Access  } | ft -property IdentityReference, AccessControlType, FileSystemRights"
pscmd = cmd1 & Chr(34) & cmd2 & cmd3 & Chr(34)
Set WshShell = CreateObject("WScript.Shell")
Set oExc = WshShell.Exec(pscmd)
Set oOutput = oExc.StdOut

The complete command looks like this:

PowerShell -executionpolicy remotesigned -Command "get-acl 'c:\temp'  | foreach-object %{ $_.Access  } | ft -property IdentityReference, AccessControlType, FileSystemRights"

Thanks,
Nick

2

What’s the reasoning behind this approach? Is the spreadsheet doing other things too?
You may get better results if you remove the ft.

If you want the data straight into Excel and have no other dependencies on the spreadsheet, I would use straight PowerShell and the Export-Excel module.

get-acl 'c:\temp' | % {$_.access} | Select IdentityReference, AccessControlType, FileSystemRights | Export-E
xcel 'c:\temp\acl.xlsx'
3

This is one piece of logic in a bigger piece of work so writing out to another spreadsheet is less than ideal. I really need to get the data out of Stdout if at all possible