将Powershell脚本嵌入VBA宏中并从那里运行 [英] Embed powershell script in VBA macro and run it from there
问题描述
我正在尝试编写这段简洁的代码，并且在最后部分遇到一些重大问题.因此，目的是编写一个代码，将其放入文件夹中的所有.zip文件中，按名称在每个文件夹中找到一个文件夹，进入内部，找到以"_nl.inp"结尾的文件，然后将其复制到存档之外./p>
在深入研究VBA的过程中，我只能找到如何解压缩整个存档的方法，而不仅仅是找到文件并将其拉出.但是我设法找到了如何使用Powershell语言来做到这一点.因此，我编写了一个功能精美的Powershell脚本.该脚本在下面.
$ zZipFileName ="C:\ Users \ ccapsun2 \ Desktop \ New加载格式\ Load Deck"$ shap =新对象-com shell.application$ folderitems = get-childitem $ zZipFileNameforeach(在$ folderitems中的$ archive){$ archive.name.substring(0，$ archive.name.length -4)$ zipFile = $ shap.Namespace($ archive.fullname)$ i = $ zipFile.items()foreach($ i在$ i中){if($ si.getfolder -ne $ null){if($ si.name -eq"LR_Front_Upper_Control_Arm"){$ fileSearch = $ si.getfolder.Items()foreach($ fileSearch中的$ sii){if($ sii.name.substring((($ sii.name.length-7)，7)-eq"_nl.inp"){$ shap.namespace($ zZipFileName).copyhere($ sii)重命名项目($ zZipFileName +"\" + $ sii.name)-新名称($ archive.name.substring(0，$ archive.name.length -4)+".inp")}}}}}} 现在，此脚本可以正常工作，但我需要将vba代码中赋予$ zZipFileName的变量和字符串"LR_Front_Upper_Control_Arm"传递给它，并且我也想将此Powershell脚本嵌入到我的VBA代码中，以便一张Excel工作表都是自给自足的.这将被我的团队中的其他人所使用，他们对编程一无所知，我或他们都不希望花时间教育他们他们具有多个脚本文件和类似的东西.
我将继续自己研究，但希望在这里能有所帮助.到目前为止，我在VBA代码中尝试过的操作如下
ShellVar = Shell("$ zZipFileName =""& folderPath&""& vbNewLine&_"$ shap =新对象-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($ i in $ i){"&vbNewLine&_"if($ si.getfolder -ne $ null){"&vbNewLine&_"if($ si.name -eq""& targetFolderName&"){"&vbNewLine&_"$ fileSearch = $ si.getfolder.Items()"&vbNewLine&_"foreach($ fileSearch中的$ sii){"&vbNewLine&_"if($ sii.name.substring(($ sii.name.length-7)，7)-eq""_nl.inp"){"&vbNewLine&_"$ shap.namespace($ zZipFileName).copyhere($ sii)"&vbNewLine&_重命名项目($ zZipFileName +""\""+ $ sii.name)-新名称($ archive.name.substring(0，$ archive.name.length -4)+"".inp")"&vbNewLine&_}"&vbNewLine&_}"&vbNewLine&_}"&vbNewLine&_}"&vbNewLine&_}"&vbNewLine&_}"，vbHide) 要尝试查看它如何工作并产生更小的恐慌，并提示您首先需要将cmd窗口置于Powershell模式，我尝试了以下小命令:
Shell"vshell& amp; amp; amp; amp; amp; amp; quot;新项目'C:\ Users \ ccapsun2 \ Desktop \ New load formatting \ load decks \ LR_Front_Upper_Control_Arm'-itemtype目录"，vbNormalFocus 良好的旧互联网提示，建议使用&&将使cmd窗口按顺序使用命令，但是再次失败了.
我也在下面的解决方案
不是您的Powershell问题的答案，但是这是您在VBA中的解决方法.我喜欢这种非递归方法来遍历常规"(非压缩)子文件夹，并设法使其适应于zip存档.
调整以适合！
公共子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设置Sh = CreateObject("Shell.Application")设置colFolders = New Collection'<<待处理的zip文件夹colFolders.添加LOCAL_ZIP'<<...从这里开始colFolders.Count>0设置fldr = Sh.Namespace(colFolders(1))colFolders.删除1对于fldr.Items中的每个itm如果是it.IsFolder然后'将此子文件夹添加到集合中进行处理colFolders.添加fldr.self.Path&"\"&itm.Name别的'是一个文件，所以看看它是否是我们想要的文件...如果itm.Name像"* _nl.inp"，那么Debug.Print itm.NameSh.Namespace(DEST_FLDR).CopyHere itm退出Do'<<如果您只希望一场比赛万一万一下一个环形结束子 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.
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
这篇关于将Powershell脚本嵌入VBA宏中并从那里运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
