Update the Excel UDF that comes with AutoIt

[water]

Done

[DerPensionist]

Thanks

[DVDRW]

Just installed Autoit and using Exel UDF that came with it.

I have Rangeread problem:

When I try to read range on same row. I get no result and no error.

_Excel_RangeRead($oWorkbook, Default, "A12:C12")

Am i missing something?

A12:A15 works just fine.

Edited November 17, 2014 by DVDRW

[water]

What is the value of @error after calling _Excel_RangeRead?

[water]

I just tried and it works fine here.

[DVDRW]

Aah sorry I didn-t know that choosing cells same row makes 2D array. Checked only concatenated 1D and 2D array result.

Thanks

[water]

[DVDRW]

Sorry another dumb question:

How I can read cell like old Excel UDF.?

I mean using integer row number and column number:

_ExcelReadCell($oExcel, 1, 1)
_ExcelRangeRead($oWorkbook, Default, ????

Edited November 21, 2014 by DVDRW

[water]

No, the new UDF only supports A1 notation, not R1C1 notation.

But there are functions in the UDF to convert A1 to R1C1 to A1.

[FlashpointBlack]

Hi Water,

I've written a small autoit script to combine like-named files. (ie: historicaltrend1.xlsx, historicaltrend2.xlsx, etc...) into one master file. Currently, I have it doing this through the _Excel_RangeRead() Function, and then putting it into a new workbook using the _Excel_RangeWrite() Function. I'm certain that using the _Excel_RangeCopyPaste() function would be MUCH quicker, but I'm uncertain how best to go about this since I have no idea where the data ends in each file (some files have 3,000 rows, others have 40,000).

The RangeRead() and RangeWrite() functions provide a nice functionality to not demand source cells and just grab everything relevant. After which, I UBound the array returned to autoit and count up a ticker in the script telling it where to RangeWrite subsequent files. Currently, the files we generate over a weekend take about 2-3 hours to process on the POS computer this is running on. Would you have any advice on what the most efficient way to do this would be using RangeCopyPaste Instead?

I'd appreciate any light you could shine on this!

Edited December 1, 2014 by FlashpointBlack

[water]

You could use _Excel_SheetCopyMove to copy a sheet to another workbook.

[FlashpointBlack]

Ah, sorry if I wasn't clear. The data from the files must be appended into a single worksheet!

[water]

I see.

To specify the varying source range you could use the "usedrange" property as described in the wiki.

[FlashpointBlack]

Aha, Ace. Thank you for the help! this cut processing time down to about 30 minutes!

[water]

Great.

To process so many records maybe a database might even be the better solution. Excel becomes quite slow with too many records.

[FlashpointBlack]

believe me, you're preaching to the choir. That's a battle I've been fighting for years. The big dogs at corporate can't see the benefits of changing a system that's worked for 15 years.

I appreciate the tip!

[water]

Convincing management is never an easy task

The system might have worked well for 15 years and there was never a need to change it. But the number of records has changed, the number of POS etc.

Ask them what they intend to do if the current system reaches a limit and crashes. How much time do they then have to let you or someone else create a new well coded and tested system? How much money will it cost them until this new system is ready? etc.

[Baraoic]

Hello,

I am having a problem trying to add an array from your AD script to an excel spreadsheet. If I try to take an array returned from the AD script and write it to excel it doesn't work, but if I first write the array to a file the read the file back into an array it works.

Here is an example:

expandcollapse popup

#include <AD.au3>
#include <Array.au3>
#include <Date.au3>
#include <Excel.au3>
#include <File.au3>
$DaysInactive = 90
$FQDN = "DC=domain,DC=com"

Local $sYear, $sMonth, $sDay
_DayValueToDate(_DateToDayValue(@YEAR, @MON, @MDAY) - $DaysInactive, $sYear, $sMonth, $sDay)
$pTime = _Date_Time_EncodeFileTime($sMonth, $sDay, $sYear)
$LLTSlimit = _LargeInt2Double(DllStructGetData($pTime, 1), DllStructGetData($pTime, 2))

_AD_Open("", "", "", StringTrimLeft(@LogonServer, 2))
If @error Then Exit MsgBox(16, "Active Directory", "Function _AD_Open encountered a problem. @error = " & @error & ", @extended = " & @extended)

$aUsersE = _AD_GetObjectsInOU($FQDN, "(&(objectCategory=person)(objectClass=user)(lastlogontimestamp<=" & $LLTSlimit & ")(!(userAccountControl:1.2.840.113556.1.4.803:=2)))", 2, "sAMAccountName,description,lastLogonTimestamp", "sAMAccountName") ;enabled users

_LastLoginTimestamp($aUsersE)
_AD_Close()

_ArrayDisplay($aUsersE)
$aUsersE[0][0] = "sAMAccountName"
$aUsersE[0][1] = "Description"
$aUsersE[0][2] = "Last Logon"


$Month = _DateToMonth(@MON, 1)
$sWorkbook = @ScriptDir & "\Accounts Disable " & $Month & " " & @YEAR & ".xls"
$oExcel = _Excel_Open(False, Default, Default, Default, True)
MsgBox(0,'open',@error & @CRLF & @extended)
$oWorkbook = _Excel_BookNew($oExcel, 1)
MsgBox(0,'booknew',@error & @CRLF & @extended)
$oExcel.ActiveSheet.Range("A:A").ColumnWidth = 21
$oExcel.ActiveSheet.Range("B:B").ColumnWidth = 125
$oExcel.ActiveSheet.Range("C:C").ColumnWidth = 15.14
_Excel_RangeWrite($oWorkbook, Default, $aUsersE, "A1")
MsgBox(0,'rw1',@error & @CRLF & @extended)
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlExcel8, True)
MsgBox(0,'save',@error & @CRLF & @extended)
_Excel_BookClose($oWorkbook)
MsgBox(0,'bookclose',@error & @CRLF & @extended)
_Excel_Close($oExcel)
MsgBox(0,'close',@error & @CRLF & @extended)

Func _LargeInt2Double($i_Low, $i_High)
    Local $i_ResultLow, $i_ResultHigh
    If $i_Low < 0 Then
        $i_ResultLow = 2 ^ 32 + $i_Low
    Else
        $i_ResultLow = $i_Low
    EndIf
    If $i_High < 0 Then
        $i_ResultHigh = 2 ^ 32 + $i_High
    Else
        $i_ResultHigh = $i_High
    EndIf
    Return Int($i_ResultLow + $i_ResultHigh * 2 ^ 32)
EndFunc   ;==>_LargeInt2Double

Func _LastLoginTimestamp(ByRef $aInput)
    For $x = 1 To UBound($aInput) - 1
        $aTemp = _AD_GetObjectProperties($aInput[$x][0], "lastLogonTimestamp")
        If $aTemp[0][0] > 0 Then
            $aInput[$x][2] = $aTemp[1][1]
        Else
            $aInput[$x][2] = "Never"
        EndIf
    Next
EndFunc   ;==>_LastLoginTimestamp

Now with that example excel reports everything was fine, but the spreadsheet appears empty when I open it and it's file size is too small so I think it really didn't work.

Though if I add:

_FileWriteFromArray(@ScriptDir & "\userse.txt", $aUsersE)
$aUsersE = ""
_FileReadToArray(@ScriptDir & "\userse.txt", $aUsersE, Default, "|")

before the $Month then it works.

Edited March 27, 2015 by Baraoic

[water]

You need at least do some error checking. Every _Excel_* function sets @error <> 0 if an error has occurred.

Never had a problem writing an array to a Worksheet.

[Baraoic]

I added message boxes after each excel function and all of them report 0 for @error and 0 for @extended except for the open is extended 1, which it should be because I'm forcing new instance. So the functions report nothing is wrong, even though it's not working for me.