# Copying a 1d array to a 2d array..

## Recommended Posts

OK, newbie type question, but here goes...

I need to load an excel sheet and copy a block of data to an array (say a 50x90 area...)

The only way I have found that works feels REALLY odd....

(Don't laugh too much...)

Open the Excel file
_ExcelBookOpen(FileOpenDialog("Load Excel Data File", "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "Data(*.xls)", 1, "RC Test Cases KY set.XLS"), 1, "False")

;Find the last cell
\$array = _excelSheetUsedRangeGet(\$oExcel, 1)

;create the master Array
Global \$numberOfCases = (\$array[3])
Global \$MasterArray[\$numberOfCases + 5][90]
Global \$grabrow[59]

Dim \$fiddle = 1
For \$Index = 1 To \$numberOfCases Step 1
\$grabrow = _ExcelReadArray(\$oExcel, \$Index, 1, 59, 0, 1)
For \$CellPos = 1 To 59
\$fiddle = \$grabrow[\$CellPos]
\$MasterArray[\$Index][\$CellPos] = \$fiddle
Next
Next

Is there a different function that can do this in 1 fell swoop?

moving an [x][y]Excel range to an [x][y]autoit array?

If not, is there a more...elegant... way to accomplish what I am doing by brute force?

Edited by everseeker

Everseeker

##### Share on other sites

In a more...general way...

Say I have a large array that is bigone[100][100]

and I am going to generate 100 1d arrays littleone[100]

How do I assign each of the littleones to 1 "row" of the bigone?

I am doing it cell by cell.... time (and memory) consuming!

but any assignment I have attempted with the 2 different dimensioned arrays in 1 line results in an error

Edited by everseeker

Everseeker

##### Share on other sites

In a more...general way...

Say I have a large array that is bigone[100][100]

and I am going to generate 100 1d arrays littleone[100]

How do I assign each of the littleones to 1 "row" of the bigone?

I am doing it cell by cell.... time (and memory) consuming!

but any assignment I have attempted with the 2 different dimensioned arrays in 1 line results in an error

Hi,

You are right, that take stime.

The 2D array copies directly from Excel, though;

Func _XLArrayRead(ByRef \$s_FilePath, \$s_i_Sheet = 1, \$s_i_ExcelValue = 1, \$s_i_Visible = 0 ,\$i_Suppress = 0)
\$var = _ExcelCOM(\$s_FilePath, \$s_i_Sheet, 1, 1, "ArrayRead", "NoSave", \$s_i_ExcelValue, \$s_i_Visible  ,\$i_Suppress, "NOTExit", "NotLastRow", "NOTToColumn"); LastRow excel
;\$var=\$s_i_ExcelValue
Return \$var
See the link in my signature; also, I think, in LocoDarwin's funcs.

Best, Randall

##### Share on other sites

Hi,

You are right, that take stime.

The 2D array copies directly from Excel, though;

Func _XLArrayRead(ByRef \$s_FilePath, \$s_i_Sheet = 1, \$s_i_ExcelValue = 1, \$s_i_Visible = 0 ,\$i_Suppress = 0)
\$var = _ExcelCOM(\$s_FilePath, \$s_i_Sheet, 1, 1, "ArrayRead", "NoSave", \$s_i_ExcelValue, \$s_i_Visible  ,\$i_Suppress, "NOTExit", "NotLastRow", "NOTToColumn"); LastRow excel
;\$var=\$s_i_ExcelValue
Return \$var
See the link in my signature; also, I think, in LocoDarwin's funcs.

Best, Randall

Ummm...

I can't find yours in there. All I can see is lots of example files and documentation. no "ExcelCom.au3" file.

What I DO see is this "...I am recommending people consider LocoDarwin's (Yet Another) ExcelCOM UDF...", So I assume you pulled yours in deference to his version.

here's the problem... LocoDarwin's (Yet Another) ExcelCOM UDF does NOT have a function that handles importing a 2d array. (at least, nothing in the version I am running: Ver 1.32 02/12/07 )

All it has is 1D array manipulation via _ExcelReadArray and _ExcelWriteArray (Which I used in the code example I showed above)

So, Do you have a copy of your ExcelCom laying around that I could use? Or could you point me to the appropriate fuction/code example from your array handling UDF (Array2D.au3) that I could use?

From my code example above...

...
\$grabRow = _ExcelReadArray(\$oExcel, \$Index, 1, 59, 0, 1)
...
How do I load the currently grabbed row (\$GrabRow) into \$MasterArray at the correct position
\$MasterArray[\$Index][????]=?????

PS Array2d has a lot of include files listed...

#include <Misc.au3>

#include <File.au3>

#include <Array.au3>

#include <GuiConstants.au3>

#include <GuiListView.au3>

are they all ...part of.... Autoit already? If not, where do I get them?

Everseeker

##### Share on other sites

Hi,

1.

no "ExcelCom.au3" file.

It is there, inside the zip at the top of the post, 1600 downloads.

2. "Array2D" ; all those includes are part of the AutoIt install; note the updated version (Array2d10.zip) in the zip at the bottom of post #1 has amendments for current AutoIt syntax, and has been working OK as far as I know.

3. Any of the examples in the zip starting "FastSubSort2" have the subsort syntax, and many show the functions changing 2D arrays to 1D arrays of delimited strings and vice-versa.

Let me know..

Best, randall

Edited by randallc

##### Share on other sites

I'm trying to learn some of the Office COM interface stuff from Locodarwin's excellent example. This is my attempt to morph his _ExcelReadArray() function into a 2D array that can read the entire active sheet by default, or be told to read a 2D range:

;===============================================================================
;
; Description:      Create a 2D array from the rows/columns of the active worksheet.
; Syntax:           _ExcelReadSheetToArray(\$oExcel, [\$iStartRow , \$iStartColumn [, \$iRowCnt, \$iColCnt]])
; Parameter(s):     \$oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   \$iStartRow - Row number to start reading, defaults to 1 (first row)
;                   \$iStartColumn - Column number to start reading, defaults to 1 (first column)
;                   \$iRowCnt - Count of rows to read, defaults to 0 (all)
;                   \$iColCnt - Count of columns to read, defaults to 0 (all)
; Requirement(s):   Requires ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns a 2D array with the specified cell contents by [\$row][\$col]
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Start parameter out of range
;                           @extended=0 - Row out of range
;                           @extended=1 - Column out of range
;                       @error=3 - Count parameter out of range
;                           @extended=0 - Row count out of range
;                           @extended=1 - Column count out of range
; Author(s):        SEO <locodarwin at yahoo dot com> (original _ExcelReadArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Note(s):          Returned array has row count in [0][0] and column count in [0][1].
;                   Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                        cell data starts at [1][1] to match R1C1 numbers.
;                   By default the entire sheet is returned.
;                   If the sheet is empty [0][0] and [0][1] both = 0.
;
;===============================================================================
Func _ExcelReadSheetToArray(\$oExcel, \$iStartRow = 1, \$iStartColumn = 1, \$iRowCnt = 0, \$iColCnt = 0)
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
Local \$iLastRow = StringInStr(\$sLastCell, "R")
Local \$iLastColumn = StringInStr(\$sLastCell, "C")
\$iLastRow = Number(StringMid(\$sLastCell, \$iLastRow + 1, \$iLastColumn - \$iLastRow - 1))
\$iLastColumn = Number(StringMid(\$sLastCell, \$iLastColumn + 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[\$iRowCnt + 1][\$iColCnt + 1]
\$avRET[0][0] = \$iRowCnt
\$avRET[0][1] = \$iColCnt

For \$r = 1 To \$iRowCnt
For \$c = 1 To \$iColCnt
\$avRET[\$r][\$c] = \$oExcel.Activesheet.Cells(\$iStartRow + \$r - 1, \$iStartColumn + \$c - 1).Value
Next
Next

;Return data
Return \$avRET

To read a 3x3 cell block starting at R7C9: _ExcelReadSheetToArray(\$oExcel, 7, 9, 3, 3)

To read the rest of the sheet from R7C9 to the end: _ExcelReadSheetToArray(\$oExcel, 7, 9)

To read two rows from R7C9 to the last column: _ExcelReadSheetToArray(\$oExcel, 7, 9, 2)

To read two columns from R7C9 to the last row: _ExcelReadSheetToArray(\$oExcel, 7, 9, 0, 2)

Hope that helps.

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

##### Share on other sites

I'm trying to learn some of the Office COM interface stuff from Locodarwin's excellent example. This is my attempt to morph his _ExcelReadArray() function into a 2D array that can read the entire active sheet by default, or be told to read a 2D range:

;===============================================================================
;
; Description:      Create a 2D array from the rows/columns of the active worksheet.
; Syntax:           _ExcelReadSheetToArray(\$oExcel, [\$iStartRow , \$iStartColumn [, \$iRowCnt, \$iColCnt]])
; Parameter(s):     \$oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   \$iStartRow - Row number to start reading, defaults to 1 (first row)
;                   \$iStartColumn - Column number to start reading, defaults to 1 (first column)
;                   \$iRowCnt - Count of rows to read, defaults to 0 (all)
;                   \$iColCnt - Count of columns to read, defaults to 0 (all)
; Requirement(s):   Requires ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns a 2D array with the specified cell contents by [\$row][\$col]
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Start parameter out of range
;                           @extended=0 - Row out of range
;                           @extended=1 - Column out of range
;                       @error=3 - Count parameter out of range
;                           @extended=0 - Row count out of range
;                           @extended=1 - Column count out of range
; Author(s):        SEO <locodarwin at yahoo dot com> (original _ExcelReadArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Note(s):          Returned array has row count in [0][0] and column count in [0][1].
;                   Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                        cell data starts at [1][1] to match R1C1 numbers.
;                   By default the entire sheet is returned.
;                   If the sheet is empty [0][0] and [0][1] both = 0.
;
;===============================================================================
Func _ExcelReadSheetToArray(\$oExcel, \$iStartRow = 1, \$iStartColumn = 1, \$iRowCnt = 0, \$iColCnt = 0)
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
Local \$iLastRow = StringInStr(\$sLastCell, "R")
Local \$iLastColumn = StringInStr(\$sLastCell, "C")
\$iLastRow = Number(StringMid(\$sLastCell, \$iLastRow + 1, \$iLastColumn - \$iLastRow - 1))
\$iLastColumn = Number(StringMid(\$sLastCell, \$iLastColumn + 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[\$iRowCnt + 1][\$iColCnt + 1]
\$avRET[0][0] = \$iRowCnt
\$avRET[0][1] = \$iColCnt

For \$r = 1 To \$iRowCnt
For \$c = 1 To \$iColCnt
\$avRET[\$r][\$c] = \$oExcel.Activesheet.Cells(\$iStartRow + \$r - 1, \$iStartColumn + \$c - 1).Value
Next
Next

;Return data
Return \$avRET

To read a 3x3 cell block starting at R7C9: _ExcelReadSheetToArray(\$oExcel, 7, 9, 3, 3)

To read the rest of the sheet from R7C9 to the end: _ExcelReadSheetToArray(\$oExcel, 7, 9)

To read two rows from R7C9 to the last column: _ExcelReadSheetToArray(\$oExcel, 7, 9, 2)

To read two columns from R7C9 to the last row: _ExcelReadSheetToArray(\$oExcel, 7, 9, 0, 2)

Hope that helps.

Yipee!!!

Ummm... by any chance, are you working on _ExcellWriteSheetFromArray() .........

Everseeker

##### Share on other sites

Yipee!!!

Ummm... by any chance, are you working on _ExcellWriteSheetFromArray() .........

By popular demand (of a total of one person):

;===============================================================================
;
; Description:      Writes a 2D array to the active worksheet
; Syntax:           _ExcelWriteSheetFromArray(\$oExcel, ByRef \$aArray [, \$iStartRow = 1, \$iStartColumn = 1 [, \$iRowBase = 1, \$iColBase = 1]])
; Parameter(s):     \$oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   \$aArray - The array ByRef to write data from (array is not modified)
;                   \$iStartRow - The table row to start writing the array to, default is 1
;                   \$iStartColumn - The table column to start writing the array to, default is 1
;                   \$iRowBase - array index base for rows, default is 1
;                   \$iColBase - array index base for columns, default is 1
; Requirement(s):   ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Parameter out of range
;                           @extended=0 - \$iStartRow out of range
;                           @extended=1 - \$iStartColumn out of range
;                       @error=3 - Array invalid
;                           @extended=0 - doesn't exist / variable is not an array
;                           @extended=1 - not a 2D array
;                       @error=4 - Base index out of range
;                           @extended=0 - \$iRowBase out of range
;                           @extended=1 - \$iColBase out of range
; Author(s):        SEO <locodarwin at yahoo dot com> (original ExcelWriteArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelWriteSheetFromArray()
; Note(s):          Default base indexes in the array are both = 1, so first cell written is from \$aArray[1][1].
;
;===============================================================================
Func _ExcelWriteSheetFromArray(\$oExcel, ByRef \$aArray, \$iStartRow = 1, \$iStartColumn = 1, \$iRowBase = 1, \$iColBase = 1)
; 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 Not IsArray(\$aArray) Then Return SetError(3, 0, 0)
Local \$iDims = UBound(\$aArray, 0), \$iLastRow = UBound(\$aArray, 1) - 1, \$iLastColumn = UBound(\$aArray, 2) - 1
If \$iDims <> 2 Then Return SetError(3, 1, 0)
If \$iRowBase > \$iLastRow Then Return SetError(4, 0, 0)
If \$iColBase > \$iLastColumn Then Return SetError(4, 1, 0)

For \$r = \$iRowBase To \$iLastRow
\$iCurrCol = \$iStartColumn
For \$c = \$iColBase To \$iLastColumn
\$oExcel.Activesheet.Cells(\$iStartRow, \$iCurrCol).Value = \$aArray[\$r][\$c]
\$iCurrCol += 1
Next
\$iStartRow += 1
Next
Return 1
EndFunc   ;==>_ExcelWriteSheetFromArray

Can't test it 'cause I don't have Excel here for the weekend. Please test if you have the chance.

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

##### Share on other sites

Can't test it 'cause I don't have Excel here for the weekend. Please test if you have the chance.

Was out of the Office...

Will test it today

Everseeker

##### Share on other sites

Was out of the Office...

Will test it today

Take note that Locodarwin has posted a new version of ExcelCOM_UDF.au3, which includes these functions and other new stuff. The new version is 1.4 dated 05 January, 2008.

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

##### Share on other sites

Take note that Locodarwin has posted a new version of ExcelCOM_UDF.au3, which includes these functions and other new stuff. The new version is 1.4 dated 05 January, 2008.

OIC!

Very nice!!!

(Congrats on the include )

Everseeker

## Create an account

Register a new account

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...