VBA - WScript.Shell - Executing A PowerShell Command

@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

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 & """"