title: Embed powershell script in VBA macro and run it from there
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>$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")
                           }
                       }
                   } 
              }
          }
       }
  </code></pre>
  
  <p>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.</p>
  
  <p>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</p>
  
  <pre><code>ShellVar = Shell("$zZipFileName = """ &amp; folderPath &amp; """" &amp; vbNewLine &amp; _
        "$shap = new-object -com shell.application" &amp; vbNewLine &amp; _
        "$folderitems = get-childitem $zZipFileName" &amp; vbNewLine &amp; _
        "foreach($archive in $folderitems){" &amp; vbNewLine &amp; _
        "$archive.name.substring(0,$archive.name.length -4)" &amp; vbNewLine &amp; _
        "$zipFile = $shap.Namespace($archive.fullname)" &amp; vbNewLine &amp; _
        "$i = $zipFile.items()" &amp; vbNewLine &amp; _
        "foreach($si in $i){" &amp; vbNewLine &amp; _
        "if($si.getfolder -ne $null){" &amp; vbNewLine &amp; _
        "if($si.name -eq """ &amp; targetFolderName &amp; """){" &amp; vbNewLine &amp; _
        "$fileSearch = $si.getfolder.Items()" &amp; vbNewLine &amp; _
        "foreach($sii in $fileSearch){" &amp; vbNewLine &amp; _
        "if ($sii.name.substring(($sii.name.length - 7),7) -eq ""_nl.inp""){" &amp; vbNewLine &amp; _
        "$shap.namespace($zZipFileName).copyhere($sii)" &amp; vbNewLine &amp; _
        "rename-item ($zZipFileName + ""\"" + $sii.name) -newname ($archive.name.substring(0,$archive.name.length -4) + "".inp"")" &amp; vbNewLine &amp; _
        "}" &amp; vbNewLine &amp; _
        "}" &amp; vbNewLine &amp; _
        "}" &amp; vbNewLine &amp; _
        "}" &amp; vbNewLine &amp; _
        "}" &amp; vbNewLine &amp; _
        "}", vbHide)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Shell "powershell &amp;&amp; new-item 'C:\Users\ccapsun2\Desktop\New loads formatting\load decks\LR_Front_Upper_Control_Arm' -itemtype directory", vbNormalFocus
  </code></pre>
  
  <p>good old internet suggesting that using &amp;&amp; will make the cmd window engage the commands sequentially but again, it didn't work.</p>
  
  <p>I have also tried the suggestion at the below <a href="https://stackoverflow.com/questions/38539489/how-to-enter-multiple-line-of-code-in-cmd-from-vba">link</a> with no success as well.</p>
  
  <p>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.</p>