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
– Andreas, Commented Nov 26, 2020 at 9:08
Regarding the problem. Maybe powershell opens the file in protected mode and/or with macros disabled? — Andreas
– Andreas, Commented Nov 26, 2020 at 9:27

Andreas · Accepted Answer · 2020-11-26 09:14:20Z

1

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.

answered Nov 26, 2020 at 9:14

Andreas

24k7 gold badges34 silver badges75 bronze badges

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

3 Comments

Rajat Solanki Over a year ago

however is there any way I can pass the arguments as well? (I am passing the path of text files and some other details to the macro via powershell script)

Andreas Over a year ago

No problem. I thought about one thing though. Maybe you want to open the file without it doing it's thing and closing directly? You could use application.ontime in workbook open to delay the run and during this delay you could have a cell you type "stop" in or a button or something that stops the code. Range(some cell).value <> "stop" then something like that...

Andreas Over a year ago

I don't think you can pass arguments. But you can write a text file in powershell, and you can read a text file in VBA. So joining the two is your "hack" solution

Collectives™ on Stack Overflow

Trigger Excel Macro, for creating text files, using PowerShell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related