JCarson Posted May 3, 2010 Share Posted May 3, 2010 Alas, not enough posts to post in the "Example Scripts Forum". This is by no means a finished script, simply a quick fix as I could feel my beard grow waiting for excel files to load. My hope is that the Excel UDF would be updated with similar code. This requires the Array2D include. These are simply a modification of the same functions in Excel UDF to allow for more of a "copy and paste" as opposed to the 1 cell at a time that was being done. This affects whole sheets only, although I figure that someone with more time than I can adapt those two functions to take advantage of this for all the variances of the function. Verified working with latest non-beta AutoIT, Windows XP SP2 with all subsequent updates and Excel 2003. Let me know if it is of any help, Carson Func _ExcelWriteSheetFromArray2($oExcel, ByRef $bArray) $AARRAY = $BARRAY _ARRAY2D_DELETE($AARRAY, 0, 1) _ARRAY2D_DELETE($AARRAY, 0, 2) _ARRAY2D_TRANSPOSE($AARRAY) With $OEXCEL.Activesheet .Range(.Cells(1, 1), .Cells(UBound($AARRAY, 2), UBound($AARRAY, 1)) ).Value = $AARRAY EndWith Return 1 EndFunc expandcollapse popupFunc _ExcelReadSheetToArray2($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = False) Local $avRET[1][2] = [[0, 0]] ; 2D return array ; Test inputs If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If $iStartRow < 1 Then Return SetError(2, 0, 0) If $iStartColumn < 1 Then Return SetError(2, 1, 0) If $iRowCnt < 0 Then Return SetError(3, 0, 0) If $iColCnt < 0 Then Return SetError(3, 1, 0) ; Get size of current sheet as R1C1 string ; Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3 Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) ; Extract integer last row and col $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3) Local $iLastRow = $sLastCell[0] Local $iLastColumn = $sLastCell[1] ; Return 0's if the sheet is blank If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET ; Check input range is in bounds If $iStartRow > $iLastRow Then Return SetError(2, 0, 0) If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0) If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0) If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0) ; Check for defaulted counts If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1 If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1 ; Size the return array ReDim $avRET[1][$iColCnt + 1] $avRET[0][0] = $iRowCnt $avRET[0][1] = $iColCnt If $iColShift Then ;Added by litlmike ; Read data to array For $r = 1 To $iRowCnt For $c = 1 To $iColCnt $avRET[$r][$c - 1] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value Next ProgressSet(($r/$iRowCnt)*100,"","Completed " & int(($r/$iRowCnt)*100) & "%") Next Else ;Default for $iColShift With $oExcel.Activesheet $avRET2=.Range(.Cells(1,1),.Cells($iRowCnt,$iColCnt)).Value _Array2D_Transpose($avRET2) _Array2D_Insert($avRET2,0,"",2) _Array2D_Add($avRET, $avRET2) $avRET2="" endwith EndIf ;Return data Return $avRET EndFunc Link to comment Share on other sites More sharing options...
PsaltyDS Posted May 3, 2010 Share Posted May 3, 2010 (edited) Yeah, we had a topic a few weeks ago where I learned you could get/put arrays via the COM interface to Excel. (I didn't know you could do that before.) It is much faster than iterating single cells, and the technique is worth incorporating in other Excel.au3 UDF functions, too.Edit: Added link to older topic Edited May 3, 2010 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
JCarson Posted May 4, 2010 Author Share Posted May 4, 2010 Apologies, despite my status on this forum, I do try to search before posting.For the record on the benefits of AutoIT, my previous solution was VBA code and was taking about 2 hours despite using "manual calculation" and "update=false" tricks amongst other things.To testify to the benefit of AutoIT, adapting the same logic of the VBA, once I changed the Excel Read/Write functions, yielded returned results in 3-6 minutes .. a 20-40x speed increase.Yeah, we had a topic a few weeks ago where I learned you could get/put arrays via the COM interface to Excel. (I didn't know you could do that before.) It is much faster than iterating single cells, and the technique is worth incorporating in other Excel.au3 UDF functions, too.Edit: Added link to older topic 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