_Excel_RangeRead "Error occurred when reading data using the transpose method."

[JeffAllenNJ]

I've been running this code for years and, suddenly, I'm getting "Error occurred when reading data using the transpose method." with the call to _Excel_RangeRead.

 

expandcollapse popup

$oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "loadAllSongs", "_Excel_Open" & @CRLF & "@error = " & @error & @CRLF & "@extended = " & @extended)

        $msg = ''
        $oWorkbook = _Excel_BookOpen($oExcel, $wbName, Default, Default, True)
        Select
            Case @error = 1
                $msg = "1: $oExcel is not an object or not an application object"
            Case @error = 2
                $msg = "2: Specified $sFilePath does not exist"
            Case @error = 3
                $msg = "3: Unable to open $sFilePath"
            Case @error = 4
                $msg = "4: "& $wbName & " already open"

        EndSelect
        If $msg <> '' Then
            ws("msg", $msg)
            $resp = MsgBox($MB_YESNOCANCEL, "loadAllSongs", "_Excel_BookOpen " & $wbName & @CRLF & @CRLF & $cellRange & @CRLF & @CRLF & $msg & @CRLF & @CRLF & "Close without saving?")
            Select
                Case $resp = $IDyes
                    _Excel_BookClose($wbName, False)
                    ws(@error,@extended)
                Case $resp = $IDCancel
                    Exit
            EndSelect
        EndIf

    $oWorkbook.WorkSheets($wsName).Activate

    $msg = ''
    $aSongs = _Excel_RangeRead($oWorkbook, $wsName, $cellRange)
    Select
        Case @error = 1
            $msg = "1: " & $wbName & " is not an object or not a workbook object"
        Case @error = 2
            $msg = "2: " & $wsName & " name or index invalid or is not a worksheet object."
        Case @error = 3
            $msg = "3: " & $cellRange & " is invalid. @extended is set to the COM error code"
        Case @error = 4
            $msg = "4: Parameter $iReturn is invalid. Has to be >= 1 and <= 4"
        Case @error = 5
            $msg = "5: Error occurred when reading data using the transpose method."
        Case @error = 6
            $msg = "6: Maximum size of an AutoIt array exceeded (2^24 = 16,777,216 elements)"
        Case @error = 7
            $msg = "7: Error occurred when reading data using the _ArrayTranspose function."
        Case @error = 8
            $msg = "8: $iReturn = 3 can only return a single cell. $vRange specified > 1 cell."
    EndSelect
    If $msg <> '' Then
        _Excel_Close($oExcel)
        ws("msg", $msg)
        Exit MsgBox($MB_SYSTEMMODAL, "loadAllSongs", "_Excel_RangeRead " & $wbName & @CRLF & @CRLF & $wsName & @CRLF & @CRLF & $cellRange & @CRLF & @CRLF & $msg)
    EndIf

 

[Andreik]

Check @extended to get the COM error code. Also please post a sample excel file so we can have a better idea where the issue might be.

[Nine]

As per help file :

Quote

The Excel transpose method has an undocumented limit on the number of cells it can transpose (depends on the Excel version).
If you transpose > 65535 cells then $bForceFunc is forced to True.
The Excel transpose method doesn't support cells with more than 255 characters.

 

[JeffAllenNJ]

Hmmm... I'm specifying range "A1:P555" which is only about 5000 cells.  I'm still getting this error.

 

FYI:  @extended = "-2147352571"

Edited May 10, 2024 by jaja714

[JeffAllenNJ]

FYI: I solved it by forcing transpose

 

$aSongs = _Excel_RangeRead($oWorkbook, $wsName, $cellRange, default, True)