_ExcelReadSheetToArray to _Excel_RangeRead

[DrewSS]

Hello,

I am having trouble upgrading AutoIT and utilizing the new Excel UDF.

Below is my old code which works great (slow, but correct), but _Excel_RangeRead is not working dynamically.

$oExcelM = _ExcelBookOpen($sFilePath_list_master)
    $aArrayM = _ExcelReadSheetToArray($oExcelM, 2)
    For $resultsM = 0 To UBound($aArrayM) - 1
        $chainM = $aArrayM[$resultsM][3]
        $storeM = $aArrayM[$resultsM][4]
        $queueM = $aArrayM[$resultsM][9]
        $descriptionM = $aArrayM[$resultsM][5]
        $categoryM = $aArrayM[$resultsM][6]
        If Not ($chainM == "") Then
            FileWrite($sFilePath_temp_master, $chainM & "-" & $storeM & @CRLF)
        EndIf
        If ($queueM == "US_L1_Call Center System Alerts") Then
            FileWrite($sFilePath_temp_master, $descriptionM & @CRLF)
        EndIf
        If ($categoryM = "Zero Coups") Then
            If ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $descriptionM & @CRLF)
            ElseIf Not ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $chainM & "-" & $storeM & @CRLF)
            EndIf
        EndIf
    Next
    _ExcelBookClose($oExcelM)

Here is the new code with _Excel_RangeRead, but the results of my array are "0"

Local $oApplM = _Excel_Open()
    Local $oExcelM = _Excel_BookOpen($oApplM, $sFilePath_list_master)
    $aArrayM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet.Usedrange.Columns("A:Z"), 1)
    For $resultsM = 0 To UBound($aArrayM) - 1
        $chainM = $aArrayM[$resultsM][3]
        $storeM = $aArrayM[$resultsM][4]
        $queueM = $aArrayM[$resultsM][9]
        $descriptionM = $aArrayM[$resultsM][5]
        $categoryM = $aArrayM[$resultsM][6]
        If Not ($chainM == "") Then
            FileWrite($sFilePath_temp_master, $chainM & "-" & $storeM & @CRLF)
        EndIf
        If ($queueM == "US_L1_Call Center System Alerts") Then
            FileWrite($sFilePath_temp_master, $descriptionM & @CRLF)
        EndIf
        If ($categoryM = "Zero Coups") Then
            If ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $descriptionM & @CRLF)
            ElseIf Not ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $chainM & "-" & $storeM & @CRLF)
            EndIf
        EndIf
    Next
    _Excel_BookClose($oExcelM)

How can I get my values from excel with the new UDF?

 

[water]

Parameter 2 is missing. Needs to be:

$aArrayM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet, $oExcelM.ActiveSheet.Usedrange.Columns("A:Z"), 1)

[DrewSS]

Good call, I just noticed that too. I used Default instead, which should be fine for my 1 worksheet. However, the range A:Z is not working.

With only 1 column at a time I suppose I could get rid of the translation variables and just make

$chainM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet, $oExcelM.ActiveSheet.Usedrange.Columns("C:C"), 1)   

(instead of $chainM = $aArrayM[$resultsM][3])

... this may work.

Unless anyone knows how to get all cells through A:Z working i'm going to change my entire program for the new structure; it just does not seem very dynamic.

[iamtheky]

maybe usedrange is the issue, what about just

$oExcelM.Activesheet.Columns("a:z")

 

[water]

What is the value of @error and @extended after _Excel_RangeRead?

[DrewSS]

When I use A:Z the errors are @5 and extended@-2147352571

I tried without Usedrange but that had the same error.

I'm changing up all my code structure but getting rid of the For loop that cycled through the complete array with the old UDF means I need a For loop for each column now?

 

 

EDIT: errors are @5 and extended@-2147352571

Edited January 14, 2015 by DrewSS

[water]

Did you have a look at the the help file?

@error = 5 means: Error occurred when reading data using the transpose method. @extended is set to the COM error code

Could you try:

$aArrayM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet, $oExcelM.ActiveSheet.Usedrange.Columns("A:Z"), 1, True)

[DrewSS]

water!!! Your the best! Thank you!

[water]

The transpose method can not handle cells with more than 255 characters. That might have caused the problem.