Sign in to follow this  
Followers 0
plastix

Execute VBA Macro with parameters ? SYNTAX

6 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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!! :D

Edited by flyingboz

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

@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 by plastix

Share this post


Link to post
Share on other sites

@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.


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

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


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

@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 :D

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0