Excel Functions/Subs Exposure?

[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,

[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")

[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 September 12, 2013 by Jags

[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

[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 September 12, 2013 by Jags

[DW1]

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

[Jags]

danwilli,

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

[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 September 13, 2013 by danwilli

[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 

[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.

[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 September 13, 2013 by Jags

[water]

To sum up all cells in the Range("A1:B5") you need:

$MySum = $oAppl.Application.WorksheetFunction.Sum($oAppl.ActiveWorkbook.Activesheet.Range("A1:B5"))

[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

Edited September 13, 2013 by danwilli

[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 September 13, 2013 by Jags

[Jags]

danwilli,

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

[water]

You can't specify

Functionx(parameter1:=value1, parameter3:=value3)

You have to use:

Functionx(value1, Default, value3)

[water]

also, you mentioned $oExcel.Run("macroname"). Where is .Run documented? What others are available?

Documentation can be found here.