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
– Stupid_Intern, Commented Mar 7, 2016 at 15:19
After this $workbook = $app.workbooks.open($excelfile) where did you enable the macro content ? See This for more info. — Stupid_Intern
– Stupid_Intern, Commented Mar 7, 2016 at 15:22
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
– Tim Williams, Commented Mar 7, 2016 at 18:15
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
– user5384290, Commented Mar 8, 2016 at 6:16

G42 · Accepted Answer · 2016-03-09 16:30:41Z

0

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

answered Mar 9, 2016 at 16:30

G42

10.1k2 gold badges22 silver badges36 bronze badges

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

3 Comments

user5384290 Over a year ago

Thanks for the answer. But still i am getting the same.

G42 Over a year ago

Are you able to manually open that node in the registry to check what the values are set to? This page discusses macro security settings. I am assuming the first part of the error message is incorrect adn the LogInformation macro is part fo the workbook you are opening (and not the Personal.XLSB or some other workbook). Edit: Also, I believe you have to run Powershell as admin to make registry changes.

Doofus Over a year ago

Not in front of my computer to test, what if you use explicit path/filename instead of variable in the workbooks.open. Pretty sure I've had this issue and found solution on this site. Path/filename might require quoting. My guess is the workbook isn't getting opened in first place.

Collectives™ on Stack Overflow

Running Excel macro from Windows PowerShell script

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related