Excel – Running Excel macro from Windows PowerShell script

excelpowershellvba

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 : COMException

Exception 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

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

Disable (after running macro)

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name AccessVBOM -PropertyType DWORD  -Value 0 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($excel.Version)\excel\Security" -Name VBAWarnings -PropertyType DWORD  -Value 0 -Force | Out-Null
Related Topic