Sign in to follow this  
Followers 0
hutch

_ExcelSheetUsedRangeGet don't work

9 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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)

:D

P.S. Tested, worked for me.

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

:D 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 by hutch

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

:)

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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
Sign in to follow this  
Followers 0