VBA - WScript.Shell - Executing A PowerShell Command

Question

I want to execute a PowerShell Command from VBA using WScript.Shell

Here is the string that gets sent to WScript.Shell:

Powershell -ExecutionPolicy Bypass -Command $FilesInPathway = get-childitem -path "C:\Users\Me\Desktop\NewFolder" -recurse -attributes !directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace(".TXT",".txt"); Rename-Item $file.fullname $tempName}

And here is the PowerShell Command I want to execute. Note this command does successfully execute in PowerShell.

$FilesInPathway = get-childitem -path "C:\Users\Me\Desktop\NewFolder" -recurse -attributes !directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace(".TXT",".txt"); Rename-Item $file.fullname $tempName}

Do not get information that there is an issue executing PowerShell Command, but the Command does not execute and files are not renamed. Msgbox returns no information. Blank form. Want to avoid switching to a PowerShell script or writing this using DIR and NAME.

strCommand = "Powershell -ExecutionPolicy Bypass -Command $FilesInPathway = get-childitem -path " & """" & dataPacket.filePath & """" & " -recurse -Attributes !Directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace(" & """" & dataPacket.stringOld & """" & "," & """" & dataPacket.stringNew & """" & "); Rename-Item $file.fullname $tempName}"
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Exec(strCommand)
strOutput = WshShellExec.StdOut.ReadAll
MsgBox strOutput

UPDATE I

New Command does traverse the directory tree and returns correct number of files in that tree. It must be the foreach function I am passing that is not executing. But when I run the debug.print version of string directly in Powershell, it executes OK - all files are renamed.

strCommand = "Powershell $FilesInPathway = get-childitem -path " & """" & dataPacket.filePath & """" & " -recurse -attributes !directory; $FilesInPathway.count"
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Exec(strCommand)
Do While WshShellExec.Status = 0
     Application.Wait (Now() + TimeValue("0:00:01"))
Loop
strOutput = WshShellExec.StdOut.ReadAll
MsgBox strOutput

The string that is passed to WshShell:

Powershell $FilesInPathway = get-childitem -path "C:\Users\Me\Desktop\NewFolder" -recurse -attributes !directory; $FilesInPathway.count

The string that is passed to PowerShell:

$FilesInPathway = get-childitem -path "C:\Users\Me\Desktop\NewFolder" -recurse -attributes !directory; $FilesInPathway.count

Note I have tried to use the StdIn.Write method to isolate Commands but am getting error.

WshShellExec.StdIn.Write "$FilesInPathway.count"

UPDATE II

Running Shell() with a type or enum declared may generate malicious macro warning and kill excel application? Have not isolated code that causes this.

The following will enter infinite loop if -noexit included with Command Do While WshShellExec.Status = 0 Application.Wait (Now() + TimeValue("0:00:01")) Loop

ANSWER

Of course this would be a syntax problem. Need to enclose parameters of Replace method with ' '. If filepath contains a whitespace, it too needs enclosure with ' '. If entered directly into PowerShell, enclosure with " " works fine. So the Command is:

strCommand = "Powershell $FilesInPathway = get-childitem -path " & "'" & dataPacket.filePath & "'" & " -recurse -attributes !directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace('" & dataPacket.stringOld & "','" & dataPacket.stringNew & "'); Rename-Item $file.fullname $tempName}"

This seems to explain why: https://stackoverflow.com/a/45762288/9721351

Answer 1

@learnAsWeGo I didn't want to post this as an answer (see my prior comments) but had trouble marking it as code and getting it into a comment. That said, if this runs for you then you can focus on editing strPoshLine so it does what you want. Might be an alternative for you.

Dim strPoshLine As String
Dim Retval As Variant

'Example1 to execute a powershell line from VBA and write host output to a file
    strPoshLine = "get-childitem -directory | Select-object fullname | out-file 'c:\PoshFromVbaTeat.txt'"
    Retval = Shell("Powershell.exe -noexit -Command " & strPoshLine, 0)

'Example2 to execute a powershell line from VBA and not exit the session so
    strPoshLine = "get-childitem -directory | Select-object fullname "
    Retval = Shell("Powershell.exe -noexit -Command " & strPoshLine, 1)
    MsgBox "Enter 'exit' in powershell window to close the session."

@learnAsWeGo, based on your comments, the following code may be more flexible for you. The -Filter can only use * and ? but the -replace expression can use regular expressions. There is output in the open session window showing renamed files. The output could be piped to a format command to suit your preference. You can review the output and exit the window when done.

Public Sub VbaToPwsh()

Dim strGetFiles As String
Dim strRename As String
Dim varRetval As Variant

strGetFiles = "Get-Childitem -Path 'C:\zDev\*' -Filter '*.txt' -Recurse -File "
' Notes:  If using -Recurse then the last '\*' in the path is not needed, otherwise needed if using -Filter.
'         The use of -Filter and -File is just to reduce the number of files going through the pipeline
'           and not needed if the -replace operator provides all the constraint required.

strRename = " Rename-Item -NewName {$_.Name -replace '.txt', '.TXT'} -PassThru "
' Notes:  The -replace comparison operator is not case sensitive on selection but writes as shown.
'         If case sensitivity on selection is needed use -creplace instead.
'         Rename-Item does not provide output and that is way the -PassThru param is added.
          
varRetval = Shell("Powershell.exe -noexit -Command " & strGetFiles & "|" & strRename, 1)

MsgBox "Enter 'exit' in PowerShell window to close the session."

End Sub

Answer 2

1. Wait until script has finished execution

You have to wait till WshShell.Exec has finished execution, as it won't signal it to you. So your code moves on and as script is still running, it hasn't returned a response and your MessageBox stays empty.

Simplest approach is to use WshShell.Run method, as that provides a parameter to wait till execution is finished.

strCommand = "Powershell -ExecutionPolicy Bypass -Command $FilesInPathway = get-childitem -path " & """" & dataPacket.filePath & """" & " -recurse -Attributes !Directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace(" & """" & dataPacket.stringOld & """" & "," & """" & dataPacket.stringNew & """" & "); Rename-Item $file.fullname $tempName}"
Set WshShell = CreateObject("WScript.Shell")
strOutput = WshShell.Run(strCommand ,0 ,True)
MsgBox strOutput

It is also possible to wait for the .Execmethod till it is finished (WshShellExec.Status <> 0), by looping and sometimes check .Status

strCommand = "Powershell -ExecutionPolicy Bypass -Command $FilesInPathway = get-childitem -path " & """" & dataPacket.filePath & """" & " -recurse -Attributes !Directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace(" & """" & dataPacket.stringOld & """" & "," & """" & dataPacket.stringNew & """" & "); Rename-Item $file.fullname $tempName}"
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Exec(strCommand)
Do While WshShellExec.Status = 0
     Application.Wait(Now() + TimeValue("0:00:01"))
Loop
strOutput = WshShellExec.StdOut.ReadAll
MsgBox strOutput

2. You can't pass multiple powershell commands from shell (without some tricks, read Docs)

Just create a ps1 file with the commands and then execute that script like above.

That will enable you to use a Powershell IDE for coding and you can reuse that scripts elsewhere! All dynamic code can be passed as arguments to the matching parameters you create in your script (saved as RenameFiles.ps1).

param ($FilePath, $StringOld, $StringNew)
$FilesInPathway = get-childitem -path $FilePath -recurse -Attributes !Directory 
foreach ($file in $FilesInPathway)
{
    $tempName = $file.name 
    $tempName = $tempName.replace($StringOld,$StringNew) 
    Rename-Item $file.fullname $tempName
}

Command to execute that script:

strCommand = "Powershell -ExecutionPolicy Bypass -File ""path\to\RenameFiles.ps1"" """ & dataPacket.filePath & """ """ & dataPacket.stringOld & """ """ & dataPacket.stringeNew & """"

Answer 3

Of course this would be a syntax problem. Need to enclose parameters of Replace method with ' '. If filepath contains a whitespace, it too needs enclosure with ' '. If entered directly into PowerShell, enclosure with " " works fine. So the Command is:

strCommand = "Powershell $FilesInPathway = get-childitem -path " & "'" & dataPacket.filePath & "'" & " -recurse -attributes !directory; foreach ($file in $FilesInPathway){$tempName = $file.name; $tempName = $tempName.replace('" & dataPacket.stringOld & "','" & dataPacket.stringNew & "'); Rename-Item $file.fullname $tempName}"

This seems to explain why: https://stackoverflow.com/a/45762288/9721351