How to use VBA Code inside a Powershell Script

Question

I want to put some Raw Data into a new Excel File with PowerShell. I'm aware how to use the comobject Excel.Application which PowerShell delivers.

Now my problem is the Data has to be processed in different ways inside the Excel (while the script is running). I already created some VBA Macros to do so a while back and now I'd like to reuse them. Is it possible to "use" the VBA through the Script inside the newly created and filled up Excel? Or do I need to recreate the VBA in Powershell?

Why not have Powershell run the saved macros after COM connection? Do note: VBA makes same COM connection to Excel object library! — Parfait
@f6a4 Thanks for that answer, but there's still a problem. I'm creating a new Excel without the VBA code in it, the Question is can I run VBA through PowerShell to the Excel without the Excel having VBA in it? — TheLinkedOne
@Parfait This sounds like it could work, but to be honest, I can't quite understand, could you explain it to me in more detail? — TheLinkedOne

Parfait Parfait · Accepted Answer · 2020-01-08T17:29:19

Essentially, VBA is a language that connects to the Excel object library. PowerShell is also a language that can connect to Excel object library. Still others including Java, C#, Python, PHP, R, and more have this facility via Component Object Model (COM) available in Windows environments.

Therefore, simply translate your VBA code to PowerShell as any Excel method should be available. VBA tends to be tied to MS Office products because it is the defacto connected language but is actually a separate component (see how it is first reference checked under Tools\References... in IDE). Below are examples of simpler VBA to PowerShell translations:

Initialize Office objects

VBA

' EARLY BINDING
Set xlObj = New Excel.Application
Set accObj = New Access.Application
Set wrdObj = New Word.Application
...

' LATE BINDING
Set xlObj = CreateObject("Excel.Application")
Set accObj = CreateObject("Access.Application")
Set wrdObj = CreateObject("Word.Application")
...

PowerShell

$xlObj = new-object -comobject excel.application
$accObj = new-object -comobject access.application
$wrdObj = new-object -comobject word.application
...

Open Office objects with Excel's Workbooks.Open, Access' OpenCurrentDatabase, Word's Documents.Open. Note: None of these methods are restricted to VBA.

VBA

Set xlWB = xlObj.Workbooks.Open("C:\Path\To\Workbook.xlsx")
Set accDB = accObj.OpenCurrentDatabase("C:\Path\To\Access.accdb")
Set wrdDOC = wrdObj.Documents.Open("C:\Path\To\Document.docx")
...

PowerShell

$xlWB = $xlObj.Workbooks.Open("C:\Path\To\Workbook.xlsx")
$accDB = $accObj.OpenCurrentDatabase("C:\Path\To\Access.accdb")
$wrdDOC = $wrdObj.Documents.Open("C:\Path\To\Document.docx")
...

Handle collections (e.g., Excel sheets, Access tables, Word paragraphs).

VBA

' SELECT SINGLE OBJECTS
Set xlSheet = xlWB.Worksheets("mySheet")
Set accTable = accObj.Currentdb().TableDefs("myTable")
Set wrdParag = wrdDOC.Paragraphs(1)
...

PowerShell

# EXCEL WORKSHEETS LOOP
$xlObj = new-object -comobject excel.application

$xlWB = $xlObj.Workbooks.Open("C:\Path\To\Workbook.xlsx")

Foreach($wsh in $xlWB.Worksheets)
{
   Write-host $wsh.name
}            

$xlWB.Close($false)
$xlObj.quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlObj )


# ACCESS DB TABLES LOOP
$accObj = new-object -comobject access.application    
$accDB = $accObj.OpenCurrentDatabase("C:\Path\To\Access.accdb")

Foreach($tbl in $accObj.CurrentDb().TableDefs())
{
   Write-host $tbl.name
}

$accDB.DoCmd.CloseDatabase
$accObj.quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($accObj )

How to use VBA Code inside a Powershell Script

1 Answers