Execute VBA Macro with parameters ? SYNTAX

[plastix]

Hi all.

I am playing around with COM automation of Excel. I can create an Excel object Ok, hide/show etc etc, and run Macro's that don't require parameters. However, trying to send parameters results in (8) : ==> The requested action with this object has failed. VBA Code:

Sub EchoText(msg As String)

MsgBox msg

End Sub

$oxl=ObjGet(@ScriptDir & "\XLwithMacro.xls")
If Not IsObj ($oxl) Then
 MsgBox(0,"Won't Work","Can't Open Workbook as object")
 Exit
EndIf
$oxl.Application.Visible = 1
$oxl.Application.Run("EchoText(""Hello"")")

Whatever variations I try are failing. Anyone got something similar to work. I'm sure I'm missing something obvious.

TIA

[flyingboz]

It takes a while to wrap your head around a lot of the syntax; it's more similar to vbscript than VBA.

It can help to look for snippets using vbscript to highlight the differences in syntax.

randallc has done a lot of work with his XL.au3 in s&s, Check out some of the properties / methods

that require parameters there. You spend enough days doing this and you start to get a real feel for what the syntax likely will be.

My first guess would be

$message = "hope i see this"
$oxl.Application.Run("EchoText",$message)

EDIT:

Tested and Working!!

Edited July 8, 2006 by flyingboz

[plastix]

@flyingboz - yep. working. thanks

Found an interesting quirk... if you repeat the call, it crashes / fails... infact, this is what was resulting in my tests from failing (i had a call to a VBA sub that didn't require parameters, then followed that by this one - and was getting errors). Try repeating the same call again... will fail. Must be losing the XL object or something.

Edited July 10, 2006 by plastix

[flyingboz]

@flyingboz - yep. working. thanks

Found an interesting quirk... if you repeat the call, it crashes / fails...

have you cleared any msgboxes or any other "blocking" xl functions?

I bet the call fails if an xl msgbox() is already raised.

[flyingboz]

@plastix -- Have you located the issue w/ multiple XL Run calls?

[plastix]

@flyingboz - you were right. the action fails because autoit cannot communicate with XL when it has an application halting event ie MsgBox. The only workaround I can think of is to make autoit poll for the messagebox, and only proceed to next command when it is closed. Thanks