senpaijp Posted May 27, 2009 Share Posted May 27, 2009 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 !!! Link to comment Share on other sites More sharing options...
reb Posted May 27, 2009 Share Posted May 27, 2009 Hi senpaijp, $oExcel.Run("Your Macro Name Here") should do it for you REB MEASURE TWICE - CUT ONCE Link to comment Share on other sites More sharing options...
senpaijp Posted May 27, 2009 Author Share Posted May 27, 2009 It does not work too.... Should I add the Worksheet name? Link to comment Share on other sites More sharing options...
reb Posted May 27, 2009 Share Posted May 27, 2009 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 Link to comment Share on other sites More sharing options...
senpaijp Posted May 27, 2009 Author Share Posted May 27, 2009 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 ! Link to comment Share on other sites More sharing options...
MrMitchell Posted May 27, 2009 Share Posted May 27, 2009 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. Link to comment Share on other sites More sharing options...
reb Posted May 27, 2009 Share Posted May 27, 2009 (edited) Arggh ......It does not work....Moreover, I have notices that AutoIt doesn't know _ExcelBookAttach but _ExcelAttach.. my lib is ExcelCOM_UDF.au3Have 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 thatREBadded: 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 May 27, 2009 by reb MEASURE TWICE - CUT ONCE Link to comment Share on other sites More sharing options...
senpaijp Posted May 28, 2009 Author Share Posted May 28, 2009 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 ... Link to comment Share on other sites More sharing options...
MrMitchell Posted May 28, 2009 Share Posted May 28, 2009 (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 May 28, 2009 by MrMitchell Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted May 28, 2009 Moderators Share Posted May 28, 2009 First of all you need to add a COM error handler. Is this macro in the same workbook you are opening? expandcollapse popup#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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now