Jags Posted September 12, 2013 Share Posted September 12, 2013 Would like to call functions I've programmed in VBA/Excel. Can I call Excel Subs from Autoit? Some guidelines to get me started could save a lot of testing time. Thanks, Link to comment Share on other sites More sharing options...
water Posted September 12, 2013 Share Posted September 12, 2013 Sure. Use the builtin Excel UDF to start Excel and open a Workbook. The returned application object ($oExcel) can then be used to run a macro: $oExcel.Run("macroname") My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jags Posted September 12, 2013 Author Share Posted September 12, 2013 (edited) Are we limited to Code module level macros scoped as public then? Is this documented? I have no problem reading/writing to cells from Autoit, but don't see any further documentation on working with the Excel. Edited September 12, 2013 by Jags Link to comment Share on other sites More sharing options...
water Posted September 12, 2013 Share Posted September 12, 2013 I'm not very familiar with macros in Excel. But everything you can do in Excel by using COM can be done with AutoIt too. Give it a try and see how it works. The help file has a lot of examples so it should be easy to start My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jags Posted September 12, 2013 Author Share Posted September 12, 2013 (edited) in VBA, last used row is returned by: LastRow = Cells(Rows.Count, 1).End(xlUp).Row How do I get this returned to Autoit? If I understand you, excel macros can execute from Autoit, but at the very least I need to pass values back to Autoit. Best would be access too all the methods and properties available in VBA in the same syntax. like: $LastRow =$oExcel.Cells(Rows.Count, 1).End(xlUp).Row also, you mentioned $oExcel.Run("macroname"). Where is .Run documented? What others are available? Edited September 12, 2013 by Jags Link to comment Share on other sites More sharing options...
DW1 Posted September 12, 2013 Share Posted September 12, 2013 You can convert the code as follows: Global Const $xlUp = -4162 $LastRow = $oExcel.Cells($oExcel.Rows.Count, 1).End($xlUp).Row That returns the last row in the first column. As for the Run method: MSDN is a great resource http://msdn.microsoft.com/en-us/library/office/ff197132.aspx AutoIt3 Online Help Link to comment Share on other sites More sharing options...
Jags Posted September 12, 2013 Author Share Posted September 12, 2013 danwilli, Does that mean that all members of the application library are available? Link to comment Share on other sites More sharing options...
DW1 Posted September 13, 2013 Share Posted September 13, 2013 (edited) danwilli, Does that mean that all members of the application library are available? Yes. As Water stated "everything you can do in Excel by using COM can be done with AutoIt too". _ExcelBookNew(), and _ExcelBookOpen() both return the Excel.Application object. _ExcelBookAttach() returns the specified workbook from the Excel.Application object. Edited September 13, 2013 by danwilli AutoIt3 Online Help Link to comment Share on other sites More sharing options...
Jags Posted September 13, 2013 Author Share Posted September 13, 2013 Sounds good, I need to some testing. To help me get moving what would Sum(A1:A10) look like? what about about the same on sheet2 Link to comment Share on other sites More sharing options...
water Posted September 13, 2013 Share Posted September 13, 2013 Best would be access too all the methods and properties available in VBA in the same syntax. You can use all methods/properties Excel provides. But you can't code AutoIt in VBA-like syntax. Named parameters aren't supported in AutoIt etc. So both languages are similar but not equal. If you run into problems, we will be happy to assist. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jags Posted September 13, 2013 Author Share Posted September 13, 2013 (edited) Looking into the includes <Excel.au3> helped shed some light on things. To summarize what I've found for others who may be learning as I am. Direct use of Excel Functions $MySum = $oExcel.Application.WorksheetFunction.Sum(2,2) Cell Values from ActiveSheet $MyVal = $oExcel.Cells(6, 1).Value * .Value is not default as in VBA, must be included $MyVal = $oExcel.Activesheet.Cells(6, 1).Value Cell Values, Qualified sheet $MySum = $oExcel.ActiveWorkbook.Sheets(2).Range("A6").Value Sum of Cells, as Range $MySum = $oExcel.Application.WorksheetFunction.Sum($oExcel.ActiveWorkbook.Sheets(2).Range("A1:A10")) Sum of Cells, Listed, delimited "," $MySum = $oExcel.Application.WorksheetFunction.Sum($oExcel.ActiveWorkbook.Sheets(2).Range("A1"),$oExcel.ActiveWorkbook.Sheets(2).Range("A2")) Edited September 13, 2013 by Jags Link to comment Share on other sites More sharing options...
water Posted September 13, 2013 Share Posted September 13, 2013 To sum up all cells in the Range("A1:B5") you need: $MySum = $oAppl.Application.WorksheetFunction.Sum($oAppl.ActiveWorkbook.Activesheet.Range("A1:B5")) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
DW1 Posted September 13, 2013 Share Posted September 13, 2013 (edited) Sum of Cells, as Range Unsolved #include <Excel.au3> $oExcel = _ExcelBookNew() Local $aArray[5] = [1,2,3,4,5] _ExcelWriteArray($oExcel, 1, 1, $aArray) _ExcelWriteArray($oExcel, 2, 1, $aArray) ;Sum gathered to variable $iSum = $oExcel.Sum($oExcel.Range("A1:E2")) _ExcelWriteCell($oExcel, "Sum = " & $iSum, "A3") ;Sum created as formula _ExcelWriteFormula($oExcel, '=CONCATENATE("Sum = ", SUM(A1:E2))', "B3") EDIT: Oops, Water beat me to it by a solid 6 minutes... my bad... damn work getting in the way of important stuff, like the AutoIt forums Edited September 13, 2013 by danwilli AutoIt3 Online Help Link to comment Share on other sites More sharing options...
Jags Posted September 13, 2013 Author Share Posted September 13, 2013 (edited) Thanks Water. Added your Solution. Forgive a newbie but what do you mean by "Named parameters aren't supported in AutoIt"? Are you referring to Passing Arguments by Value from VBA Routines(Macros) back to Autoit? Edited September 13, 2013 by Jags Link to comment Share on other sites More sharing options...
Jags Posted September 13, 2013 Author Share Posted September 13, 2013 danwilli, Thanks. Your solution works too. For now I'm exploring using excel functions from autoit without writing to cells. Link to comment Share on other sites More sharing options...
water Posted September 13, 2013 Share Posted September 13, 2013 You can't specify Functionx(parameter1:=value1, parameter3:=value3) You have to use: Functionx(value1, Default, value3) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted September 13, 2013 Share Posted September 13, 2013 also, you mentioned $oExcel.Run("macroname"). Where is .Run documented? What others are available? Documentation can be found here. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki 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