将 VBS 嵌入 PowerShell [英] Embed VBS into PowerShell
问题描述
我们在这里有一个应用程序可以将数据转储到 CSV 文件中,然后由另一个脚本使用.在应用程序的前一个迭代中,其中一列是user_name"并包含用户 ID.那仍然存在,但它现在将_company"附加到用户 ID,因此现在列中的用户 ID 不再是tim",而是tim_company".我们正在考虑剥离公司,我认为最好的方法是将一些 VB 作为函数嵌入到现有脚本中,就像这个网站建议的那样:http://www.out-web.net/?p=130.
We have an application here that dumps data into a CSV, which is then used by another script. In the previous iteration of the application, one of the columns was "user_name" and contains user IDs. That is still there, but it now appends "_company" to the user ID, so instead of the user ID "tim" it is now "tim_company" in the column. We are looking to strip the company off, and I thought the best way would be to embed some VB into the existing script as a function, like this site suggested: http://www.out-web.net/?p=130.
这是我尝试添加的内容:
This is what I've tried to add:
function Load-VbsCode{
param(
$Code = $(throw "No VBS code specified.")
)
# Convert an array of multiple text lines to a string
$vbsCode = [string]::Join("`n", $Code)
$vbs = New-Object -ComObject 'MSScriptControl.ScriptControl'
$vbs.Language = "VBScript"
$vbs.AddCode($vbsCode)
$vbs.CodeObject
}
# pass the entire vbs file to Load-VbsCode
$vbs = Load-VbsCode $(Get-Content .\ReplaceData.vbs)
# Ready to use the Removenavient function
$c = $vbs.RemoveData
.vbs 只是删除/替换数据,如下所示:
The .vbs is just to remove/replace data as shown here:
Function Removedata
Const FromValue = "_company"
Const ToValue = ""
Dim objExcel : Set objExcel = CreateObject("Excel.Application")
'objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("D:\Location\JunkData.csv")
Dim objWorksheet : Set objWorksheet = objWorkbook.Worksheets(1)
'Dim objRange : Set objRange = objWorksheet.UsedRange
objWorksheet.Cells.Replace FromValue, ToValue
objExcel.DisplayAlerts = False
objExcel.Save
objExcel.Quit
End Function
我收到此错误:
New-Object : Retrieving the COM class factory for component with CLSID
{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC} failed due to the following error:
80040154.
At D:\Location\Remove__company_from_CSV.ps1:11 char:22
+ $vbs = New-Object <<<< -ComObject 'MSScriptControl.ScriptControl'
+ CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException
+ FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand
Property 'Language' cannot be found on this object; make sure it exists and
is settable.
At D:\Location\Remove__company_from_CSV.ps1:12 char:10
+ $vbs. <<<< Language = "VBScript"
+ CategoryInfo : InvalidOperation: (Language:String) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound
You cannot call a method on a null-valued expression.
At D:\Location\Remove__company_from_CSV.ps1:13 char:17
+ $vbs.AddCode <<<< ($vbsCode)
+ CategoryInfo : InvalidOperation: (AddCode:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
我不会VB,也不知道如何在PS中调用VBScript.我读到这可能是一个丢失的 DLL,但我更有可能相信我没有调用我应该调用的东西.有什么想法吗?
I don't know VB, and I don't know how to call VBScript in PS. I read this might be a missing DLL, but I'm more likely to believe I'm not calling something I should be calling. Any ideas?
推荐答案
我和其他人一样,因为我认为你不必要地使事情复杂化.如果您只想剥离公司,那么import-csv
并循环进行更改.
I'm with everyone else in that I think you are unnecessarily complicating things. If all you want to do is strip the company off then import-csv
and loop to make your changes.
给定保存在文件中的示例 CSV
Given the sample CSV saved in file
user_name,first_name,last_name
lrivera0_company,Lawrence,Rivera
tlawrence1_company,Theresa,Lawrence
rboyd2_company,Roy,Boyd
cperry3_company,Christine,Perry
jmartin4_company,Jessica,Martin
针对它运行以下代码.
$filepath = "d:\temp\text.csv"
$toReplace = "_company"
(Import-Csv $filepath) | ForEach-Object{
$_.User_Name = ($_.User_Name).Replace($toReplace,""); $_
} | Export-CSV $filepath -NoTypeInformation
这会将文件作为 PowerShell 对象读取.对于每条记录,我们通过删除_company"来更新user_name
(警告是我希望你没有像_company_company"这样的帐户).使用 $_
将更新的记录推回到管道中,然后再推到原始文件中.请像我一样先用虚拟数据测试这个.
That will read the file as a PowerShell object. For every record we update the user_name
by removing "_company" (caveat being I hope you don't have accounts with that in there already like "_company_company"). Use $_
to push the updated record back into the pipe and out again to the original file. Please test this with dummy data first like I did.
这篇关于将 VBS 嵌入 PowerShell的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!