Automate running vba code on excel file via Powershell

Question

OS: Windows 10, 1903
Office: Office 2016
Powershell: 5.1

I have an excel file we'll call records.xlsm that gets automatically downloaded every day. I need to do things to that file and then send it on someplace else. I have created the necessary VBA code to automate this and that code resides in a .bas file we'll call cleanup.bas .

I'm attempting to find a way to use Powershell or some other canned Microsoft tool to automate running the vba code against the file. I haven't found a way (that I like) to do this yet with Powershell.

My goal is to use task scheduler to run a Powershell script to make the changes in records.xlsm using the code from cleanup.bas.

I don't mind if cleanup.bas has to be imported into records.xlsm but that process must also be automated too.

I assume it's possible. I'm asking to confirm / deny if it is or not. If possible, please tell me where I need to start looking documentation-wise.

I found code from here. I don't mind going that route, but it seems not so elegant. Practicality trumps Pretty though, so I'll use it if nothing else works.

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $false

$workbook = $xl.Workbooks.Open("c:\temp\test.xls")
$xlmodule = $workbook.VBProject.VBComponents.Add(1)

$code = @"
sub cleanup()
'your code goes here
end sub
"@

$xlmodule.CodeModule.AddFromString($code)

Thanks for the time.

Answer 1

Continuing from my comment...

Yet, if you'd search (never stop at the first one you find) the SO post, via the search box above; you'll see similar Q&A on the use case you say you want.

How to use VBA Code inside a Powershell Script

Or just a web search:

https://www.excell-en.com/blog/2018/8/20/powershell-run-macros-copy-files-do-cool-stuff-with-power

https://powershelladministrator.com/2017/12/20/open-excel-file-and-run-macro/

Idea's and likability of a resolution, if there is one, comes from personal research.

Update:

Continuing from my comment, as of your last comment to me.

You can read any text-based file into PowerShell, That does not mean PowerShell can run it. External code must be ran using the needed parsing engine. Again, in your use case the MSOffie VBA engine.

I could refactor your code to call a serialized .bas file this way.

$xl               = New-Object -ComObject Excel.Application
$xl.Visible       = $true
$xl.DisplayAlerts = $false

$workbook = $xl.Workbooks.Open("c:\temp\test.xls")
$xlmodule = $workbook.VBProject.VBComponents.Add(1)

$VbaCode = (Get-Content -Path '$PWD\SomeCode.bas' -Raw)

$xlmodule.CodeModule.AddFromString($VbaCode)

Update

One more item is that, if you do not have these two keys...

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name AccessVBOM -Value 1 -Force | 
Out-Null

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name VBAWarnings -Value 1 -Force | 
Out-Null

...set on the host you are trying to do this on, this...

$xlmodule = $workbook.VBProject.VBComponents.Add(1)

...will fail anyway because the VBComponents do not exist until you set them.

Answer 2

I'm not that familiar with setting up macros in Office applications. But I've often run VBS against Outlook COM objects, which turns out to be very similar to the VBA code. That said, PowerShell can instantiate COM objects! I've seen many people do the same sort of thing purely inside PowerShell, including for Excel. In this paradigm it wouldn't really be a macro, but an external script that cracks open the XLSX file makes the modifications and save it back.

My advice would be to check out the ImportExcel PowerShell module, which you can find on the Gallery. It's very capable, doesn't require that Excel be installed and it's more of a first class citizen than COM. I realize that might entail a significant rewrite on your end, but my bet is you'll be happier with the final product.