Excel UDF - Help & Support

[illostos]

I'm using Microsoft Excel 2010 (14.0.7116.5000 (32-Bit))

My Autoit Version is v3.3.8.1

[water]

Can you please try the latest AutoIt beta version? It comes with the most current version of the new Excel UDF included.

[illostos]

The Beta Version worked.

Thanks, I didn't know that the code was based on the newest beta Version

[JLogan3o13]

I have been promising myself to look into the Excel rewrite for some time but haven't had the opportunity, so I know I am coming late to the party.

I am now working on a script that requires the latest beta, so figured it was the perfect time. I am noticing one function in particular that I use a lot did not make it into the rewrite (_ExcelReadSheetToArray). Can you please explain if there is a method for doing this in the new UDF (ExcelRangeRead?)?

Edited May 19, 2014 by JLogan3o13

[water]

According to the help file

$aAllCells = _Excel_RangeRead($oWorkbook) ; Read all used cells of the active worksheet
$aAllCells = _Excel_RangeRead($oWorkbook, 2) ; Read all used cells of worksheet 2
$aAllCells = _Excel_RangeRead($oWorkbook, "Test") ; Read all used cells of worksheet named "Test"

[JLogan3o13]

Thanks, must have skipped over that as I do not see that simple a notation in the help file.

The closest I came was something like this: _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange.Columns("A:F"), 1). I will try your example.

Edited May 19, 2014 by JLogan3o13

[KarlosTHG]

Hi guys, I have been using this UDF and found a problem, I am using BEx Analyzer wich is an addin for Excel, this addin connects the aplication to a SAP server with a little login dialog, In my script I need to automatize this, so I do this:

-Open the Excel with the addin with its own launcher             [OK]

-Open the XLS file I need            [OK]

-Call from Autoit the Addin macro that refresh the file with the last data   [OK]

*At this step the login dialog appears, now the problem is that my script stops here until I close this dialog, but I need to continue the script so it can enter the credentials automatically.

 

I know I can use another process to put the login info, but I want to keep it as simple as possible (one .exe only).

I even did some Event handler in the worksheet so it trigger the macro when a cell value was 1, so I only had to write this value from Autoit, but the result is the same, the execution stops until I close the login window.

How can I return to the code execution as soon as I trigger the macro?

Thanks!

Edited May 27, 2014 by KarlosTHG

[water]

As AutoIt does no multitasking and a Window waiting for user input is a blocking function there is nothing you can do but start a second script to handle the window before you run the macro.

Please have a a look at the OutlookEX UDF where this approach is used to click away Outlook security warnings.

[dar100111]

Hey Water!

Love the Excel UDF's.  Took me a second to get used to it but they work great.

Where can I learn more about all the extensions (not sure if I said that the right way) like $oExcel.Columns.AutoFit ?

I just saw another user's script with this example and was wondering what all you can do with the code.

Wanted to get a better understanding.

Thanks!

[water]

On MSDN you'll find the Excel Object Model Reference.

[dar100111]

I consider myself pretty new to coding still but I think this will change my life.  Thanks!

[Bearpocalypse]

Due to power issues on my desktop I built myself a new one. When I downloaded AutoIt and ran my latest project I freaked out when I got slammed with all kinds of errors. That will teach me not to pay attention to what version I'm downloading.

Anyway,

I have a function that writes lots of stuff based on the last empty row. You may remember from my last topic started Water. That function was

_ExcelWriteCell($oExcel, $Data, $newRow, #)

I have, the excelwrite command(the excel book, the data, the next empty row, number of the column to write data to).

Now, do I need to convert the row to a number using _Excel_ColumnToNumber to be able to get this working again?

$newRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 ;This is working great still when finding the next empty row.

$ACol = _Excel_ColumnToNumber('A')
_Excel_RangeWrite($oBook, 'Sheet1', $Data, $ACol)

This is what I have been beating around with, obviously it is wrong. Do I need to start listing the columns into arrays now or do I just suck and the answer is much easier? If I specify the cell, ie A2875 instead of $ACol, then the data gets written.

[water]

Use

$iNewRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 ;This is working great still when finding the next empty row.
_Excel_RangeWrite($oBook, 'Sheet1', $Data, "A" & $iNewRow)

because the UDF only supports "A1" notation.

[Bearpocalypse]

Derp, I didn't even think about using that. I guess that's why you are the super smart one. Many thanks!

[water]

I spent so much time in rewriting the UDF - it's easy to be smart then