Embed powershell script in VBA macro and run it from there

Question

I am trying to write this neat little bit of code and am having some massive issues with the last bit of it. So the intention was to write a code that would go into all the .zip files in a folder, find a folder in each by name, go inside, find a file ending in "_nl.inp" and copy it outside the archive.

While digging for ideas as far as VBA goes I could only find how to unzip the entire archive and not just pinpoint the file and pull i out. But I managed to find how to do this in powershell language. So I wrote a powershell script which works beautifully. That script is below.

$zZipFileName = "C:\Users\ccapsun2\Desktop\New loads formatting\load decks"
      $shap = new-object -com shell.application
      $folderitems = get-childitem $zZipFileName
      foreach($archive in $folderitems){
        $archive.name.substring(0,$archive.name.length -4)
        $zipFile = $shap.Namespace($archive.fullname) 
        $i = $zipFile.items()
        foreach($si in $i){ 
            if($si.getfolder -ne $null){
                 if($si.name -eq "LR_Front_Upper_Control_Arm"){ 
                     $fileSearch = $si.getfolder.Items()
                     foreach($sii in $fileSearch){
                         if ($sii.name.substring(($sii.name.length - 7),7) -eq "_nl.inp"){
                              $shap.namespace($zZipFileName).copyhere($sii)
                              rename-item ($zZipFileName + "\" + $sii.name) -newname ($archive.name.substring(0,$archive.name.length -4) + ".inp")
                         }
                     }
                 } 
            }
        }
     }

Now, this script works fine but I need to pass to it the variable that is given to $zZipFileName and the string "LR_Front_Upper_Control_Arm" from my vba code and I also want to embed this powershell script in my VBA code so that it is all self-contained withing one excel sheet. This will get used by other in my team who don't have a clue about programming and neither I or they don't want to spend time educating them that they have multiple script files and stuff like that.

I will continue researching on my own but was hoping to get a little help here. What I've tried so far with my VBA code is the below

ShellVar = Shell("$zZipFileName = """ & folderPath & """" & vbNewLine & _
      "$shap = new-object -com shell.application" & vbNewLine & _
      "$folderitems = get-childitem $zZipFileName" & vbNewLine & _
      "foreach($archive in $folderitems){" & vbNewLine & _
      "$archive.name.substring(0,$archive.name.length -4)" & vbNewLine & _
      "$zipFile = $shap.Namespace($archive.fullname)" & vbNewLine & _
      "$i = $zipFile.items()" & vbNewLine & _
      "foreach($si in $i){" & vbNewLine & _
      "if($si.getfolder -ne $null){" & vbNewLine & _
      "if($si.name -eq """ & targetFolderName & """){" & vbNewLine & _
      "$fileSearch = $si.getfolder.Items()" & vbNewLine & _
      "foreach($sii in $fileSearch){" & vbNewLine & _
      "if ($sii.name.substring(($sii.name.length - 7),7) -eq ""_nl.inp""){" & vbNewLine & _
      "$shap.namespace($zZipFileName).copyhere($sii)" & vbNewLine & _
      "rename-item ($zZipFileName + ""\"" + $sii.name) -newname ($archive.name.substring(0,$archive.name.length -4) + "".inp"")" & vbNewLine & _
      "}" & vbNewLine & _
      "}" & vbNewLine & _
      "}" & vbNewLine & _
      "}" & vbNewLine & _
      "}" & vbNewLine & _
      "}", vbHide)

To try and see how it works more small scare and getting a hint that you first need to put the cmd window into powershell mode I tried this small command:

Shell "powershell && new-item 'C:\Users\ccapsun2\Desktop\New loads formatting\load decks\LR_Front_Upper_Control_Arm' -itemtype directory", vbNormalFocus

good old internet suggesting that using && will make the cmd window engage the commands sequentially but again, it didn't work.

I have also tried the suggestion at the below link with no success as well.

I have been making slow progress at the expense of frustration for about 3 days now. Any help would be much appreciated. I am probably gonna have a mental breakdown and start crying soon haha.

Answer 1

Not an answer to your Powershell problem, but here's how you'd do it in VBA. I like this non-recursive approach to traversing "regular" (non-zipped) subfolders, and managed to adapt it to work with a zip archive.

Adjust to suit!

Public Sub UnzipSpecificFiles()

    Const LOCAL_ZIP As Variant = "C:\_Stuff\temp\test.zip"
    Const DEST_FLDR As Variant = "C:\_Stuff\temp\"

    Dim Sh As Object, ns, itm, colFolders, fldr

    Set Sh = CreateObject("Shell.Application")

    Set colFolders = New Collection '<< zip folders to be processed
    colFolders.Add LOCAL_ZIP     '<<    ...starting here

    Do While colFolders.Count > 0
        Set fldr = Sh.Namespace(colFolders(1))
        colFolders.Remove 1
        For Each itm In fldr.Items
            If itm.IsFolder Then
                'add this subfolder to the collection for processing
                colFolders.Add fldr.self.Path & "\" & itm.Name
            Else
                'is a file, so see if it's the one we want...
                If itm.Name Like "*_nl.inp" Then
                    Debug.Print itm.Name
                    Sh.Namespace(DEST_FLDR).CopyHere itm
                    Exit Do '<< if you only expect one match
                End If
            End If
        Next itm
    Loop

End Sub

Answer 2

Tim's answer is a lot better than my overly complicated solution but if anyone ever wonders how to get it to work I changed the code to be like the below.

Public FSO As New FileSystemObject
Dim b
Set b = VBA.CreateObject("WScript.Shell")
Set a = FSO.CreateTextFile("C:\Temp\temp.ps1")
a.WriteLine ("$zZipFileName = '" & folderPath & "'")
a.WriteLine ("      $shap = new-object -com shell.application")
a.WriteLine ("      $folderitems = get-childitem $zZipFileName")
a.WriteLine ("      foreach($archive in $folderitems){")
a.WriteLine ("        $archive.name.substring(0,$archive.name.length -4)")
a.WriteLine ("        $zipFile = $shap.Namespace($archive.fullname)")
a.WriteLine ("        $i = $zipFile.items()")
a.WriteLine ("        foreach($si in $i){")
a.WriteLine ("            if($si.getfolder -ne $null){")
a.WriteLine ("                 if($si.name -eq '" & targetFolderName & "'){")
a.WriteLine ("                     $fileSearch = $si.getfolder.Items()")
a.WriteLine ("                     foreach($sii in $fileSearch){")
a.WriteLine ("                         if ($sii.name.substring(($sii.name.length - 7),7) -eq '_nl.inp'){")
a.WriteLine ("                              $shap.namespace($zZipFileName).copyhere($sii)")
a.WriteLine ("                              rename-item ($zZipFileName + '\' + $sii.name) -newname ($archive.name.substring(0,$archive.name.length -4) + '.inp')")
a.WriteLine ("                         }")
a.WriteLine ("                     }")
a.WriteLine ("                 }")
a.WriteLine ("            }")
a.WriteLine ("         }")
a.WriteLine ("      }")

ShellVar.Run "powershell ""C:\Temp\temp.ps1""", vbNormalFocus

The last problem with this is that I couldn't figure out how to make the VBA code wait for the powershell script to finish. I tried adding , WaitOnReturn at the end of the .Run command but it wasn't waiting. If that would be fixed then I'd only add something to get rid of the script file created. Something like Kill a.

folderPath and targetFolderName are variables passed from the main macro.