Embed VBS into PowerShell

Question

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

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

I'm getting this error:

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

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?

I thought it would make more sense to just do it in powershell? Here's a link that shows you how to do a find and replace on a text file: blogs.technet.com/b/heyscriptingguy/archive/2008/01/17/… — Nick.Mc
– Nick.Mc, Commented Oct 19, 2015 at 23:44
Using VB is technicaly doable, but it's really not a good idea. — JPBlanc
– JPBlanc, Commented Oct 20, 2015 at 3:12
Thanks. I'm going to research this immediately so I minimize how much of a waste of time I add to your lives. — Nate
– Nate, Commented Oct 20, 2015 at 13:15

Matt · Accepted Answer · 2015-10-20 03:58:42Z

3

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.

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

Run the following code against it.

$filepath = "d:\temp\text.csv"
$toReplace = "_company"
(Import-Csv $filepath) | ForEach-Object{
    $_.User_Name = ($_.User_Name).Replace($toReplace,""); $_
} | Export-CSV $filepath -NoTypeInformation

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.

answered Oct 20, 2015 at 3:58

Matt

47.1k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nate Over a year ago

I bow to your obvious genius, sir. I thought VB was the way to go because this CSV can be upwards of 80MB on some days, but your approach works where mine did not, so thanks again.

Kev · Accepted Answer · 2015-10-20 05:29:44Z

3

The real underlying cause of this is because MSScriptControl.ScriptControl is a 32 bit library and you're most likely running your PowerShell script in a 64 bit shell. This won't work, you can't load 32 bit binaries into 64 bit processes and vice versa.

You'd need to execute your PowerShell script in a 32 bit shell to get MSScriptControl.ScriptControl to instantiate. You can do this one of two ways:

From the command line using:

c:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe .\yourscript.ps1

From the GUI:

https://technet.microsoft.com/en-us/library/hh847733.aspx

However, what you're doing is really cumbersome and I'd take Matt's advice and rewrite your thing in PowerShell, it'll be easier to debug and maintain in the long run.

answered Oct 20, 2015 at 5:29

Kev

120k53 gold badges308 silver badges396 bronze badges

1 Comment

Nate Over a year ago

Thank you for your assistance. I will research this.

Collectives™ on Stack Overflow

Embed VBS into PowerShell

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related