Calling Powershell from VBA

Welcome Forums General PowerShell Q&A Calling Powershell from VBA

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
8 months ago.

  • Author
    Posts
  • #95819

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 4
    Rank: Member

    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

      Participant
      Points: 0
      Rank: Member

      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

The topic ‘Calling Powershell from VBA’ is closed to new replies.