Running Excel macro from Windows PowerShell script

Question

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

The first line of the error describes the reason: either you haven't enabled the macros explicitly via powershell cmd or there isn't any macros that's what comes to my mind for now — Stupid_Intern
After this $workbook = $app.workbooks.open($excelfile) where did you enable the macro content ? See This for more info. — Stupid_Intern
Your error message doesn't match your script - what macro are you trying to run, how is it declared, and what module is it in? — Tim Williams
Thanks you very much. I am using excel 2010. My Macros are in work book level General. And Powershell windows powwershell script in windows 7. powershell.ps1 — user5384290

G42 G42 · Accepted Answer · 2016-03-09T16:30:41

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

Running Excel macro from Windows PowerShell script

1 Answers