I want to run below Excel macro from Windows PowerShell script.
Public Sub LogInformation()
Dim strFile_Path As String
strFile_Path = Application.ThisWorkbook.FullName & ".txt"
Open strFile_Path For Append As #1
Write #1, "message As String" & " : Logged at " & Now
Close #1
End Sub
My PowerShell
#Call the application
$excel = new-object -comobject excel.application
#Now we select the file and path
$excelFile = Get-ChildItem -Path "..\McroWPSS.xlsm"
#The next variable speeds the script up by not calling the comobject as often
$app = $excel.Application
#Now we open the Excel file and activate the macro enabled content
$workbook = $app.workbooks.open($excelfile)
#The next command makes Excel visible
$app.Visible = $false
$workbook.Activate()
#Now we run all the Macros that need to be run.
$app.Run("LogInformation")
#Now we save the workbook in the standard daily format and the close Excel
$workbook.save()
$workbook.close()
$excel.quit()
When I run my PowerShell script I get
Exception calling "Run" with "1" argument(s): "Cannot run the macro 'Workbook_Open'. The macro may not be available in
this workbook or all macros may be disabled."
At D:\Powershell\practice\MacroRun.ps1:16 char:2
+ $app.Run("Workbook_Open")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : COMExceptionException calling "Save" with "0" argument(s): "'McroWPSS.xlsm' is read-only. To save a copy, click OK, then give the
workbook a new name in the Save As dialog box."
At D:\Powershell\practice\MacroRun.ps1:19 char:2
+ $workbook.save()
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
Best Answer
You can control the macro enabled/disbaled via the registry. That's how I got mine to run.
Enable
Disable (after running macro)