Sign in to follow this  
Followers 0
senpaijp

Call a VBA macro of a Excel file

10 posts in this topic

Dear All,

I am a newbie with AUtoIt3.

I'd like to call a VBA macro located in Excel file.

#include <ExcelCOM_UDF.au3>
        $oExcel = _ExcelBookOpen ("<chemin>\titi.xls", 1)

        If @error = 1 Then
            MsgBox (0, "Error!", "Unable to start Excel object")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "File does not exist")
            Exit
        EndIf
       
        _ExcelWriteCell ($oExcel, "coucou", 1, 1) ; it works fine :))))


        $oExcel.Application.Run ("ma_macro")   ; it does not work :(((
       
        _ExcelBookClose ($oExcel)oÝ÷ Ù8^zºè®g¬±¨Í7ê1qé@¦bq«b¢tn«¨¶f¦iÊèªê-Ó~¨
i'¶*'Féêºfjf®®¢ÑDäD®º+N«z«²×iËb¢|"¶aÊç-«j)^u8^f®¬½êò²)©æ®¶­se7V"F÷Fò¢&ævRgV÷C´bgV÷C²å6VÆV7@¢7FfT6VÆÂåFWBÒgV÷CµDõDògV÷C°¤VæB7V

Where is the mistake ?

Thank you !!!

Share this post


Link to post
Share on other sites



Hi senpaijp,

$oExcel.Run("Your Macro Name Here") should do it for you

REB


MEASURE TWICE - CUT ONCE

Share this post


Link to post
Share on other sites

It does not work too....

Should I add the Worksheet name?

Share this post


Link to post
Share on other sites

It does not work too....

Should I add the Worksheet name?

Play with this. It is the complete code I use with your book name inserted.

Global $bBook = "<chemin>\titi.xls"

If WinExists("Microsoft Excel - titi.xls") Then

_ExcelBookAttach($bBook)

Else

$oExcel = _ExcelBookOpen($bBook)

EndIf

_ExcelBookAttach($oExcel)

WinWait($oExcel)

$oExcel.Run("ma_macro")

REB


MEASURE TWICE - CUT ONCE

Share this post


Link to post
Share on other sites

Arggh ......

It does not work....

Moreover, I have notices that AutoIt doesn't know _ExcelBookAttach but _ExcelAttach.. my lib is ExcelCOM_UDF.au3

Have you tried the code ?

Thank you for your help REB !

Share this post


Link to post
Share on other sites

To execute a macro, you might need this format:

$oExcel.Application.Run("BookName.xls!Macro1")

Name of workbook instead of BookName.xls and name of macro instead of Macro1. Just tested on Excel 2007 and it worked for me. Not sure how spaces are handled but mine didn't have any spaces.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Arggh ......

It does not work....

Moreover, I have notices that AutoIt doesn't know _ExcelBookAttach but _ExcelAttach.. my lib is ExcelCOM_UDF.au3

Have you tried the code ?

Thank you for your help REB !

Ok. I should have noticed that but didn't. I am using #include <Excel.au3>

Try that

REB

added: Yes I am using the code and coping text from my e-mail and inserting it into an excel workbook. The text is then processed by an Excel Macro.

Edited by reb

MEASURE TWICE - CUT ONCE

Share this post


Link to post
Share on other sites

I have tried

$oExcel.Application.Run("BookName.xls!Macro1") trasformed in:
$oExcel.Application.Run ("Feuil1!toto") and $oExcel.Application.Run ("Automatisation!toto")
but it does not run.

I send a screen shot of my Excel tree with this message.

Hope you'll be able to help me ...

post-50095-1243496875_thumb.jpg

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Try:

$oExcel.Application.Run("SettingExtractor_V0.xls!toto")

Need to use FileName, not Workbook or Sheet name. At least that's what I had to do...

Edited by MrMitchell

Share this post


Link to post
Share on other sites

First of all you need to add a COM error handler. Is this macro in the same workbook you are opening?

#include <Excel.au3>

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$oExcel = _ExcelBookOpen("c:\temp\book1.xlsm", 1)
If @error Then
    ConsoleWrite("Error opening Excel Workbook" & @CR)
    Exit
EndIf
_ExcelMacroRun($oExcel, "Sheet1.toto")

;===============================================================================
;
; Function Name:    _ExcelMacroRun()
; Description:      Runs a Visual Basic macro
; Parameter(s):     $o_object           - Object variable of a Excel.Application object
;                   $s_MacroName        - The name of the macro. Can be any combination of template,
;                                           module, and macro name. (See Remarks)
;                   $v_Arg1             - Optional: The first parameter to pass to the macro
;                   ...                 ...
;                   $v_Arg30            - Optional: The thirtieth parameter to pass to the macro
; Requirement(s):   AutoIt3 Beta with COM support (post 3.1.1)
; Return Value(s):  On Success  - Returns 1
;                   On Failure  - Returns 0 and sets @ERROR
;                   @ERROR      - 0 No Error
;                               - 2 Com Error
;                               - 3 Invalid Data Type
;                   @Extended   - Contains invalid parameter number
; Remark(s):
; Author(s):        Bob Anthony (big_daddy)
;
;===============================================================================
;
Func _ExcelMacroRun(ByRef $o_object, $s_MacroName, $v_Arg1 = Default, $v_Arg2 = Default, $v_Arg3 = Default, $v_Arg4 = Default, $v_Arg5 = Default, $v_Arg6 = Default, $v_Arg7 = Default, $v_Arg8 = Default, $v_Arg9 = Default, $v_Arg10 = Default, $v_Arg11 = Default, $v_Arg12 = Default, $v_Arg13 = Default, $v_Arg14 = Default, $v_Arg15 = Default, $v_Arg16 = Default, $v_Arg17 = Default, $v_Arg18 = Default, $v_Arg19 = Default, $v_Arg20 = Default, $v_Arg21 = Default, $v_Arg22 = Default, $v_Arg23 = Default, $v_Arg24 = Default, $v_Arg25 = Default, $v_Arg26 = Default, $v_Arg27 = Default, $v_Arg28 = Default, $v_Arg29 = Default, $v_Arg30 = Default)

    If Not IsObj($o_object) Then
        SetError(3, 1)
        Return 0
    EndIf
    ;
    $o_object.Run($s_MacroName, $v_Arg1, $v_Arg2, $v_Arg3, $v_Arg4, $v_Arg5, _
            $v_Arg6, $v_Arg7, $v_Arg8, $v_Arg9, $v_Arg10, _
            $v_Arg11, $v_Arg12, $v_Arg13, $v_Arg14, $v_Arg15, _
            $v_Arg16, $v_Arg17, $v_Arg18, $v_Arg19, $v_Arg20, _
            $v_Arg21, $v_Arg22, $v_Arg23, $v_Arg24, $v_Arg25, _
            $v_Arg26, $v_Arg27, $v_Arg28, $v_Arg29, $v_Arg30)

    If @error Then
        SetError(2)
        Return 0
    EndIf

    SetError(0)
    Return 1
EndFunc   ;==>_ExcelMacroRun

Func MyErrFunc()

    ConsoleWrite("We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & Hex($oMyError.number, 8) & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext & @CRLF)

    Local $err = $oMyError.number
    If $err = 0 Then $err = -1

    $g_eventerror = $err ; to check for after this function returns
EndFunc   ;==>MyErrFunc

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