Jump to content
Sign in to follow this  
Jags

Excel Functions/Subs Exposure?

Recommended Posts

Jags

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Jags

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

danwilli,

Does that mean that all members of the application library are available?

Share this post


Link to post
Share on other sites
DW1

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
Jags

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Jags

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
DW1

 

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
Jags

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
Jags

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water
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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - 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  

×