Jump to content
Sign in to follow this  
Jags

Excel Functions/Subs Exposure?

Recommended Posts

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,

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

danwilli,

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

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...