_ExcelSheetUsedRangeGet don't work

[hutch]

this is a very simple code but it dont work !!!

and I don't know why because it chould

#include <ExcelCOM_UDF.au3> ; Include the collection

Local $oExcel = _ExcelBookopen(@ScriptDir & "\test.xls",1)

$array = _ExcelSheetUsedRangeGet($oExcel,1)

MsgBox(0, "last cell",$array[0])

_ExcelBookClose($oExcel)

the error message is

\\chy70\csuint\_Scripts_2008\Support gestion\ExcelCOM_UDF.au3 (2303) : ==> Subscript used with non-Array variable.:

$aSendBack[2] = Number($sTemp[1])

$aSendBack[2] = Number($sTemp^ ERROR

I use ExcelCOM_UDF.au3 1.4

[PsaltyDS]

Probably didn't open in the first place. Try it this way:

#include <ExcelCOM_UDF.au3> ; Include the collection

Local $oExcel = _ExcelBookopen(@ScriptDir & "\test.xls",1)
$iErrorSav = @error
If IsObj($oExcel) = 0 Or $iErrorSav <> 0 Then
    MsgBox(16, "Error opening Excel book!", "$oExcel = " & $oExcel & "  @error = " & $iErrorSav)
    Exit
EndIf
$array = _ExcelSheetUsedRangeGet($oExcel,1)
MsgBox(0, "last cell",$array[0])
_ExcelBookClose($oExcel)

P.S. Tested, worked for me.

Edited January 23, 2008 by PsaltyDS

[hutch]

it dont work

allways the same error message with your script

when I launch the script excel open with the good file and on the good sheet but msgbox don't appear but this error message

\\chy70\csuint\_Scripts_2008\Support gestion\ExcelCOM_UDF.au3 (2303) : ==> Subscript used with non-Array variable.:

$aSendBack[2] = Number($sTemp[1])

$aSendBack[2] = Number($sTemp^ ERROR

I am on office 2003

Edited January 24, 2008 by hutch

[hutch]

I this that there is an error in ExcelCOM_UDF.au3 1.4 because this script work

#include <ExcelCOM_UDF.au3>; Include the collection

Local $oExcel = _ExcelBookopen(@ScriptDir & "\test.xls",1)

$lastcell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address
$lastcell = StringReplace($lastcell, "$", "")
    
MsgBox(0, "last cell",$lastcell)
_ExcelBookClose($oExcel)

Edited January 24, 2008 by hutch

[PsaltyDS]

I this that there is an error in ExcelCOM_UDF.au3 1.4 because this script work

#include <ExcelCOM_UDF.au3>; Include the collection

Local $oExcel = _ExcelBookopen(@ScriptDir & "\test.xls",1)

$lastcell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address
$lastcell = StringReplace($lastcell, "$", "")
    
MsgBox(0, "last cell",$lastcell)
_ExcelBookClose($oExcel)

It works fine for me but I believe you, and that's the right code at the right line for v1.4 of the UDF (the latest). I'll have to see what it takes to force duplication of your symptoms.

[Zest]

Hmm I have the exact some problem here ... :-(

[PsaltyDS]

Hmm I have the exact some problem here ... :-(

I was never able to duplicate the symptoms.

Can you post a short reproducer script that will create a new .xls file, write some data in it, and then get the error with _ExcelSheetUsedRangeGet()? Otherwise, you're going to have to post a file that shows the issue.

[ohgod]

same problem here with one of your older script as well as the one in this thread

I don't think it has anything to do with the file

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sPath = @ScriptDir & "\diatrimmed.xls"  ; has data to N3
$vSheet = 'Sheet1'

$oExcel_1 = _ExcelBookOpen ($sPath)
If IsObj($oExcel_1) Then
    $avRange= _ExcelSheetUsedRangeGet($oExcel_1, $vSheet)

    If IsArray($avRange) Then
        _ArrayDisplay($avRange, "Debug: $avRange"); [0] = N3, [1] = R3C14, [2] = 14

       ; An Excel range looks like "A1:N3"
       ;   Alternative range with integers:  _ExcelCopy($oExcel_1, 1, 1, $avRange[2], $avRange[3])
        If _ExcelCopy ($oExcel_1, "A1:" & $avRange[0]) Then
            $oExcel_2 = _ExcelBookNew (1)
            If Not _ExcelPaste ($oExcel_2, "A1:" & $avRange[0]) Then
                MsgBox(16, "Error", "_ExcelPaste() failed." & @CRLF & _
                        "@error = " & @error & "  @extended = " & @extended)
            EndIf
        Else
            MsgBox(16, "Error", "_ExcelCopy() failed." & @CRLF & _
                    "@error = " & @error & "  @extended = " & @extended)
        EndIf
    Else
        MsgBox(16, "Error", "_ExcelSheetUsedRangeGet() failed.")
    EndIf
Else
    MsgBox(16, "Error", "_ExcelBookOpen() failed.")
EndIf

I was never able to duplicate the symptoms.

Can you post a short reproducer script that will create a new .xls file, write some data in it, and then get the error with _ExcelSheetUsedRangeGet()? Otherwise, you're going to have to post a file that shows the issue.

[PsaltyDS]

same problem here with one of your older script as well as the one in this thread

I don't think it has anything to do with the file

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sPath = @ScriptDir & "\diatrimmed.xls" ; has data to N3
$vSheet = 'Sheet1'

$oExcel_1 = _ExcelBookOpen ($sPath)
If IsObj($oExcel_1) Then
    $avRange= _ExcelSheetUsedRangeGet($oExcel_1, $vSheet)

    If IsArray($avRange) Then
        _ArrayDisplay($avRange, "Debug: $avRange"); [0] = N3, [1] = R3C14, [2] = 14

      ; An Excel range looks like "A1:N3"
      ;   Alternative range with integers:  _ExcelCopy($oExcel_1, 1, 1, $avRange[2], $avRange[3])
        If _ExcelCopy ($oExcel_1, "A1:" & $avRange[0]) Then
            $oExcel_2 = _ExcelBookNew (1)
            If Not _ExcelPaste ($oExcel_2, "A1:" & $avRange[0]) Then
                MsgBox(16, "Error", "_ExcelPaste() failed." & @CRLF & _
                        "@error = " & @error & "  @extended = " & @extended)
            EndIf
        Else
            MsgBox(16, "Error", "_ExcelCopy() failed." & @CRLF & _
                    "@error = " & @error & "  @extended = " & @extended)
        EndIf
    Else
        MsgBox(16, "Error", "_ExcelSheetUsedRangeGet() failed.")
    EndIf
Else
    MsgBox(16, "Error", "_ExcelBookOpen() failed.")
EndIf

Thanks. I am not currently operating in the Slough of MS Despond, but I'll try the reproducer when I can.