water Posted July 12, 2014 Author Share Posted July 12, 2014 Done My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
DerPensionist Posted July 13, 2014 Share Posted July 13, 2014 Thanks Link to comment Share on other sites More sharing options...
DVDRW Posted November 17, 2014 Share Posted November 17, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted November 17, 2014 Author Share Posted November 17, 2014 What is the value of @error after calling _Excel_RangeRead? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 17, 2014 Author Share Posted November 17, 2014 I just tried and it works fine here. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
DVDRW Posted November 18, 2014 Share Posted November 18, 2014 Aah sorry I didn-t know that choosing cells same row makes 2D array. Checked only concatenated 1D and 2D array result. Thanks Link to comment Share on other sites More sharing options...
water Posted November 18, 2014 Author Share Posted November 18, 2014 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
DVDRW Posted November 21, 2014 Share Posted November 21, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted November 21, 2014 Author Share Posted November 21, 2014 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
FlashpointBlack Posted December 1, 2014 Share Posted December 1, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted December 1, 2014 Author Share Posted December 1, 2014 You could use _Excel_SheetCopyMove to copy a sheet to another workbook. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
FlashpointBlack Posted December 1, 2014 Share Posted December 1, 2014 Ah, sorry if I wasn't clear. The data from the files must be appended into a single worksheet! Link to comment Share on other sites More sharing options...
water Posted December 1, 2014 Author Share Posted December 1, 2014 I see. To specify the varying source range you could use the "usedrange" property as described in the wiki. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
FlashpointBlack Posted December 1, 2014 Share Posted December 1, 2014 Aha, Ace. Thank you for the help! this cut processing time down to about 30 minutes! Link to comment Share on other sites More sharing options...
water Posted December 1, 2014 Author Share Posted December 1, 2014 Great. To process so many records maybe a database might even be the better solution. Excel becomes quite slow with too many records. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
FlashpointBlack Posted December 2, 2014 Share Posted December 2, 2014 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! Link to comment Share on other sites More sharing options...
water Posted December 2, 2014 Author Share Posted December 2, 2014 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Baraoic Posted March 26, 2015 Share Posted March 26, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted March 26, 2015 Author Share Posted March 26, 2015 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Baraoic Posted March 27, 2015 Share Posted March 27, 2015 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now