Trigger Excel Macro, for creating text files, using PowerShell

Question

I am using the following PowerShell script to trigger an Excel macro.
The Excel macro processes existing text files and creates new text files.

The Excel macro on its own is working, but when I trigger it using PowerShell, the text files are not created.

$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("C:\Pre_Post_Remote.xlsm")
$excel.Run("pre_post_remote", $fpath, $ptype, $ftype)
$workbook.save()
$workbook.close()
$excel.quit()

There are no errors. It runs and nothing happens. I suspect it is because of Scripting.FileSystemObject used within the macro to create text file.

Can't you have VBA code in workbook open and all you need to do in powershell is to open the file? Meaning you also move save and close of the workbook to the VBA code. So powershell is done when the workbook opens. — Andreas
Regarding the problem. Maybe powershell opens the file in protected mode and/or with macros disabled? — Andreas

Andreas Andreas · Accepted Answer · 2020-11-26T09:14:20

Keep only the first two lines of the powershell code and remove the rest.

In VBA you move your code from module to thisworkbook_open and at the end of your VBA code you add

ThisWorkbook.Save
ThisWorkbook.Close

Unless there is more to your powershell and VBA code than you said, then this should function the same.

Trigger Excel Macro, for creating text files, using PowerShell

1 Answers