Calling Powershell from VBA

This topic contains 2 replies, has 2 voices, and was last updated by  Nick White 2 months, 2 weeks ago.

  • Author
    Posts
  • #95819

    Nick White
    Participant

    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

  • #95846

    Matt Bloomfield
    Participant

    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
      Participant

      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.