call excel macro with powershell

Question

I'm trying to open an excel sheet, run the macro and close it periodically to get an E-Mail notification when a date in a cell changes to "xxx".

Unfortuantely, powershell shows an error when running the macro below:

Sub tata(ByVal Target As Range) On Error Resume Next If Target.Cells.Count < 1 Then Exit Sub Set xRg = Intersect (Range("AT28:AT673"), Target If xRg Is Nothing Then Exit Sub If IsNumeric(Target.Value) And Target.Value = 1 Then Call Mail_small_Text_outlook End If End Sub Sub Mail_small_Text_Outlook() Dim xOutApp As Object Dim xOutMail As Object Dim xMailBody as String Set xOutApp = Create.Object("Outlook.Application") Set xOutMail = xOut.App.CreateItem(0) xMailBody = "Hi" & vbNewLine & _ "" On error Resume Next With xOutMail .To = "[email protected]" .CC = "" .BCC = "" .Subject = "order" .Body = "xMailbody .Send End With On Error GoTo 0 Set xOutMail = Nothing Set xOutApp = Nothing End Sub

Ausnahme beim Aufrufen von "Run" mit 1 Argument(en): "Parameter nicht optional. (Ausnahme von HRESULT: 0x8002000F (DISP_E_PARAMNOTOPTIONAL))" In Zeile:14 Zeichen:2 + $App.Run("tata") + ~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : COMException

 $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled $app = New-Object -comobject Excel.Application $app.Visible = $True $app.DisplayAlerts = $False $wb = $App.Workbooks.Open("c:\my.xlsm") $App.Run("tata") $app.ActiveWorkbook.Saves $app.Quit()

sodawillow · Accepted Answer · 2019-03-08 16:50:09Z

1

You need to pass an argument (Target) when calling the macro :

$App.Run("tata", "value for target")

answered Mar 8, 2019 at 16:50

sodawillow

13.3k4 gold badges36 silver badges44 bronze badges

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

2 Comments

Jow Doe Over a year ago

Hi, I don't get it :-)

sodawillow Over a year ago

When running the macro you need to specify a value after the name of the macro, it is used in the first part of the code. You could also avoid the first block by calling "Mail_small_Text_Outlook" instead of "tata", then you wouldn't need to pass any argument $App.Run("Mail_small_Text_Outlook")

Collectives™ on Stack Overflow

call excel macro with powershell

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related