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