_ExcelSortExtended

[Kiti]

Hello!

I want to sort by a collumn in an excel workbook, using Locodarwin's UDF.

But I don't get the right parameters, here's the error I get:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Administrator\Desktop\2 iunie\3\script3.au3"   
   C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (1225) : ==> The requested action with this object has failed.:
   $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection, $oExcel.Range($sKey), Default, $iDirection,  $oExcel.Range($sKey), $iDirection, $iHeader, True, $fMatchCase, $iOrientation, Default, $iDataOption)
   $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey)^ ERROR
   >Exit code: 1    Time: 0.726

It seems like the $sKey parameter is wrong, but I cannot fiind the right one.

Here's my script:

#include <ExcelCOM_UDF.au3>


$oExcel = _ExcelAttach("C:\Documents and Settings\Administrator\Desktop\Book3.xls", "FilePath")


_ExcelSortExtended($oExcel, "I", 1 , 1, 10000, 24, 2, 1, False,  2, 0)



Msgbox(0,"", @error & " ; " & @extended)

Thank you in advance!

[PsaltyDS]

Hello!

I want to sort by a collumn in an excel workbook, using Locodarwin's UDF.

But I don't get the right parameters, here's the error I get:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Administrator\Desktop\2 iunie\3\script3.au3"   
   C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (1225) : ==> The requested action with this object has failed.:
   $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection, $oExcel.Range($sKey), Default, $iDirection,  $oExcel.Range($sKey), $iDirection, $iHeader, True, $fMatchCase, $iOrientation, Default, $iDataOption)
   $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey)^ ERROR
   >Exit code: 1    Time: 0.726

It seems like the $sKey parameter is wrong, but I cannot fiind the right one.

Here's my script:

#include <ExcelCOM_UDF.au3>

$oExcel = _ExcelAttach("C:\Documents and Settings\Administrator\Desktop\Book3.xls", "FilePath")

_ExcelSortExtended($oExcel, "I", 1 , 1, 10000, 24, 2, 1, False,  2, 0)

Msgbox(0,"", @error & "; " & @extended)

Thank you in advance!

First, here's the function:

expandcollapse popup

;===============================================================================
;
; Description:    Performs an advanced sort on a range.
; Syntax:          _ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;                       $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sKey - The key column or row to sort by (in A1 format)
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColStart - The starting column for the number format(left) (default=1)
;                   $iRowEnd - The ending row for the number format (bottom) (default=1)
;                   $iColEnd - The ending column for the number format (right) (default=1)
;                   $iDirection - Sort direction (1=Ascending, 2=Descending) (default=descending)
;                   $iHeader - Assume sort data has a header?  (1=yes, 2=No, 0=guess) (default=guess)
;                   $fMatchCase - Match case when performing sort (True|False)
;                   $iOrientation - Specify how sort data is arranged (1=sort columns, 2=sort rows) (default=sort rows)
;                   $iDataOption - Specify how sort will treat data (0=sort normal, 1=sort text as numbers) (default = sort normal)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Starting row or column invalid
;                           @extended=0 - Starting row invalid
;                           @extended=1 - Starting column invalid
;                       @error=3 - Ending row or column invalid
;                           @extended=0 - Ending row invalid
;                           @extended=1 - Ending column invalid
; Author(s):        SEO <locodarwin at yahoo dot com>, many thanks to DaLiMan
; Note(s):        This sort routine will not function properly with pivot tables.  Please
;                   use the pivot table sorting functions instead.
;
;===============================================================================
Func _ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort _
            ($oExcel.Range($sKey), $iDirection, $oExcel.Range($sKey), Default, $iDirection, $oExcel.Range($sKey), _
            $iDirection, $iHeader, True, $fMatchCase, $iOrientation, Default, $iDataOption)
    Else
        $oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection, $oExcel.Range($sKey), Default, _
            $iDirection, $oExcel.Range($sKey), $iDirection, $iHeader, True, $fMatchCase, $iOrientation, Default, $iDataOption)
    EndIf
    Return 1
EndFunc;==>_ExcelSortExtended

I don't have MSOffice on this machine (happily using OOo), so I can't test right now. But I think you're wrong right from the second parameter. Specify "I1" vice just "I" for the 9th column. You don't expect to have to give a row number just to specify the column, but it makes that a complete "A1" type reference.

[Kiti]

ok, I've tried to simplify it as much as possible. Here's the resulting code:

#include <ExcelCOM_UDF.au3>
$oExcel = _ExcelAttach("C:\Documents and Settings\Administrator\Desktop\Book3.xls", "FilePath")
_ExcelSort($oExcel, "A1", 1, 1, 10, 1, 1)

Book3.xls is a worksheet with numbers written on column 1, from line 1 to line 10.

And it still doesn't sort them, even though it seems like it worked fine:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Administrator\Desktop\2 iunie\3\script3.au3"   
 >Exit code: 0  Time: 0.634

What am I doing wrong?