_Excel_RangeWrite: How to transpose data?

[twinsen]

I'm just new to AutoIt but not rather new to programming.

 

I have an array of [1, 2, 3, 4, 5] and want to write to Excel as:

1 2 3 4 5

but the result is always:

1
2
3
4
5

Putting range in $vRange parameter just gets the first element of array to transpose:

_Excel_RangeWrite($oExcel, "test_sheet", $myarray, "C1:G1")

the result is:
1 1 1 1 1

How can I do what I want using _Excel_RangeWrite?

[Jfish]

The last optional parameter in the excel function is supposed to use the _array_transpose method if set to true.  It seemed to ignore that when I just tried it (could be me it is before full coffee intake at the moment).  This is a workaround:

$transposeArray=_ArrayTranspose ($myArray )

If you add that transpose function on the line before the excel function with the proper array name it should work (although you should be able to do that in the excel function too).

@water the transpose parameter of the function seems to be a bit off (again, could be me).  I am on 3.3.12.0 but I checked the change log and did not see any recent changes to the function. 

Edited April 15, 2016 by Jfish

[water]

Twinsen,

what you have is a 1D array with 5 rows. To write all values jnto a single row you need a 2D array with a single row and 5 columns. 

[water]

Jfish,

an array is in format row/column but Excel needs it column/row (god knows why). That's what the transpose method/function is for.  Nothing else. 

[water]

Twinsen,

You could try

_Excel_RangeWrite($oExcel, "test_sheet", $myarray, "C1")

 

[Jfish]

@water I suppose I was thrown by the help file that says "True forces to use the _ArrayTranspose function instead of the Excel transpose method"  because if you call _arrayTranspose separately before the excel function it behaves appears to operate differently than if you set that parameter to true.  It looks like transpose was changed in 3.8.10 (not sure if could accept 1D or 2D arrays as input before that?).  I only mention it because the Excel UDF contains a comment that "ArrayTranspose only works for 2D arrays so we do it ourselfs for 1D arrays".  I am super punchy today so please forgive me if this is still way off base but would you expect the code below to work the same with or without a call to _arrayTranspose?

#include <Array.au3>
#include <Excel.au3>

global $myArray =[1,2,3,4,5]

; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

;$transposeArray=_ArrayTranspose ($myArray )
$result=_Excel_RangeWrite($oWorkbook, "Sheet1", $myArray,"A1:A6",True,True)

 

[water]

Can't test at the moment because I'm in vacation. I would expect a different result. Did you test it yourself?

[Jfish]

@water I did and am a bit confused.  It does, as you predict, produce different results.  I think two things are happening.  First, the OP used a vertical range for the output so that was a no-go.  However, the second part is still confusing to me.  The difference between:

$transposeArray=_ArrayTranspose ($myArray )
$result=_Excel_RangeWrite($oWorkbook, "Sheet1", $myArray,"A1")

and this: 

$result=_Excel_RangeWrite($oWorkbook, "Sheet1", $myArray,"A1", true,true)

The former creates a horizontal range write and the latter a vertical even though (I think and please correct me) that last parameter in the second example should be doing the same thing as in the first example according to the help file "...forces to use the _ArrayTranspose function".  Again, not sure I am reading this right ... just checking with you as the expert.

[water]

The last parameter does not change the result you get. It only uses another way to transpose the array to the format needed by Excel and to bypass some Excel limitations. 

[Jfish]

I know, but isn't the other way it transposes the use of _ArrayTranpose?  If so, I would think the two examples above would produce the same result?    

[water]

No, in example one you do the transpose twice. RangeWrite always transposes your array. You can only decide which method to use. 

[Jfish]

Ah, okay.  Thanks for hanging in there with me on the explanation.

[water]

[twinsen]

Thank you @Jfish and @water for your replies.

 

Yes, _ArrayTranspose($myArray) solved my problem.  And here's what I tested after reading your replies.

Local $myArray = [1,2,3,4,5]

1)
_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", $myArray, "A1:D1", True, True)
Result: 1 2 3 4 5

2)
_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", $myArray, "A1:D1", True, False)
Result: 1 2 3 4 5

3)
_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", $myArray, "A1")
Result: 1 2 3 4 5

4)
;_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", _ArrayTranspose($myArray), "A1")
Result: 1

5)
;_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", _ArrayTranspose($myArray), "A1:D1")
Result: 1 1 1 1 1

6)
;_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", _ArrayTranspose($myArray), "A1:D1", True, False)
Result: 1 1 1 1 1

7)
;_ArrayTranspose($myArray)
_Excel_RangeWrite($oExcel, "test_sheet", _ArrayTranspose($myArray), "A1:D1", True, True)
Result: 1 1 1 1 1

Before I created this topic, I put _ArrayTranspose() as in 4), 5), 6), 7).  Too bad I didn't find example usage of _ArrayTranspose() in _Excel_RangeWrite function reference.

 

Thanks again for your help.

[twinsen]

I posted a reply to my topic, but it is hidden.  What did I do wrong or is it by the system?

 

Also, how to update topic to "[Solved] ....." ?

 

Thanks

[water]

To mark it as solved modify post #1.