How do I get the return value of a Powershell script that is run in Excel VBA?

Question

I am using Excel VBA to run a simple Powershell script that, when run in Powershell itself, returns one or two file names of some files within a given directory.

I can run this script from my VBA, but the return value is always some random integer. How do I get the script to return the file names that were returned through the Powershell script?

VBA to call script:

 Dim weekly As String

 weekly = Shell("Powershell ""<location of powershell script.ps1>"" ")

Script:

Get-ChildItem "<directory to search in>" | Where-Object {$_.Name -match "<regex to find file name>"}

If I have missed any details, please ask.

trincot · Accepted Answer · 2017-08-02 20:46:33Z

1

I know this is way late but I wanted to let anyone else searching for this a way to do this. Have your powershell script write an output file:

Get-ChildItem "Directory to Search" | Select-String -Pattern "what to seach for" | out-file "C:\output.txt"

From there you can write this line by line to a variable:

Sub test()
Dim txtfile, text, textline As String

txtfile = "C:\output.txt"

Open txtfile For Input As #1

Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
Loop

Close #1

MsgBox text

End Sub

From there you can insert the text into a cell, or if preferred, you can write each line to an array and select each line individually if needed.

edited Aug 2, 2017 at 20:46

trincot

357k38 gold badges282 silver badges338 bronze badges

answered Aug 2, 2017 at 20:33

iAndrewJohnson

263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user7868 · Accepted Answer · 2024-09-11 00:44:37Z

1

You can use WScript to access StdOut output of a PowerShell script. Use the following code:

Set wShell = CreateObject("WScript.Shell")
Set wShellOutput = wShell.Exec("powershell 'C:\Path\To\file.ps1'")
text_output = wShellOutput.StdOut.ReadAll ()

I would suggest using single quotes instead of double quotes in the .Exec() call, because Windows handles double quotes painfully.

Edit: as noted by @Belladonna, you would need to tell VBA to wait until the PowerShell script finishes (e.g. here WScript.Shell.Exec - read output from stdout).

edited Sep 11, 2024 at 0:44

answered Apr 12, 2023 at 1:57

user7868

3161 silver badge8 bronze badges

3 Comments

Belladonna Over a year ago

I like this concept as it saves having to output the results to file. However it didn't work for me and all I got was the empty string. Does WScript.Shell.Exec wait for the script to complete before returning?

user7868 Over a year ago

I believe so - I haven't messed with this in a while. Maybe test it with Start-Sleep? I also can't remember whether this read text returned by the script, or text printed to the console.

Belladonna Over a year ago

Turns out it doesn't wait, so you have to poll for output. There's a good thread on it here, with VBA code examples: stackoverflow.com/questions/32492879/…

Adam Bertram · Accepted Answer · 2013-11-05 01:41:18Z

0

It looks like the VBA is simply seeing the exit code that the powershell.exe quits with. Try this:

Get-ChildItem "<directory to search in>" | Where-Object {$_.Name -match "<regex to find file name>"} 2>&1

I believe this will redirect your Powershell output to stdout which the VBA should be able to pick up.

answered Nov 5, 2013 at 1:41

Adam Bertram

4,2884 gold badges25 silver badges29 bronze badges

1 Comment

HighHopes Over a year ago

I was so excited for this to work! It didn't, however. Do you think there may be another way of doing this?

Belladonna · Accepted Answer · 2024-08-19 09:11:26Z

Try this the Powershell function below to run a powershell command and return the output. The code outputs the result of the command to a temporary file, waits for the command to complete, and returns the output.

The Powershell function uses sub WaitFile to wait for the output file to be completed, which signals completion of the powershell command, and uses the Windows sleep function to wait 100ms when polling.

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function Powershell(Command As String) As String

    'Runs powershell command and returns output
    
    Dim FilePath As String
    Dim OutFile As String
    Dim fso As Object
    
    'Generate output file name
    FilePath = ThisWorkbook.Path
    Set fso = CreateObject("Scripting.FileSystemObject")
    OutFile = FilePath & "\" & fso.GetTempName()
    Set fso = Nothing
    
    'Delete output file if it exists
    DelFile OutFile
        
    'Add output file to command
    Command = SPrintF("%s | out-file -FilePath %s -Encoding ASCII", Command, OutFile)
    
    'Run command
    Shell SPrintF("Powershell ""%s""", Command), vbMinimizedNoFocus
    
    'Wait for temp file to appear
    WaitFile OutFile
    
    'Gather results from temp file
    Powershell = GetFile(OutFile)
    
    'Delete output file if it exists
    DelFile OutFile

End Function

Sub WaitFile(FileName As String, Optional TimeOutSecs As Long = 60)

    'Returns entire contents of file
    
    Dim fhan As Long
    Dim s As String
    Dim StartTime As Date

    On Error Resume Next
    
    'Initialise start time
    StartTime = Now
    
    'Wait for file to appear (or time out)
    Do While FileLen(FileName) = 0
        Sleep 100
        DoEvents
        If DateDiff("s", StartTime, Now) > TimeOutSecs Then Exit Do
    Loop
   
End Sub

Collectives™ on Stack Overflow

How do I get the return value of a Powershell script that is run in Excel VBA?

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related