CODEWITHSUNDEEP
powershellexcelreturn-valuevba
HighHopes
HighHopes

Reputation: 2112

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

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.

Upvotes: 3

Views: 7391

Answers (4)

user7868
user7868

Reputation: 306

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).

Upvotes: 1

Belladonna
Belladonna

Reputation: 308

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

Upvotes: 0

iAndrewJohnson
iAndrewJohnson

Reputation: 26

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.

Upvotes: 1

Adam Bertram
Adam Bertram

Reputation: 4198

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.

Upvotes: 0

Related Questions