Jump to content

Recommended Posts

Posted

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,

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted (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 by Jags
Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted (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 by Jags
Posted (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 by danwilli
Posted

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 

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted (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 by Jags
Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted (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 :P

Edited by danwilli
Posted (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 by Jags
Posted

danwilli,

Thanks.  Your solution works too.  For now I'm exploring using excel functions from autoit without writing to cells.  

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted
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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...