Calling Powershell from VBA

This topic contains 2 replies, has 2 voices, and was last updated by  Nick White 5 days, 10 hours ago.

  • Author
  • #95819

    Nick White

    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"


  • #95846

    Matt Bloomfield

    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'
    • #95849

      Nick White

      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

You must be logged in to reply to this topic.