twinsen

_Excel_RangeWrite: How to transpose data?

16 posts in this topic

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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. 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Twinsen,

You could try

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@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)

 


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@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.


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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. 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

To mark it as solved modify post #1.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now