Handling COM-objects inside COM-objects

I have had great troubles getting the Excel sort function to work via COM, because it
has an internal (Range) object reference, so the wonderline (for me) was:

$RC = $oXl.Selection.Sort($oXl.ActiveSheet.Range("$ByCol1"),$Order1,,,,,,$xlHeader)

My first thought was that Excel would create the internal object, so this would work:
$RC = $oXl.Selection.Sort(ActiveSheet.Range("$ByCol1"),$Order1,,,,,,$xlHeader) ; DOSN'T WORK

!!! [Smile] But fortunately for us, Ruud has made it possible to call objects within objects [Smile] !!!

This can ome in handy in a lot of situations where you want to convert small VB-Scripts to KiX

Cut'n paste code:

;example of use
$SortStartRow = "2" ; First datarow
$xlHeader = 1 ; 1=Yes 2=No
$oXl = CreateObject("Excel.Application")
$RC = $oXl.Workbooks.Open("Test.xls")
xlSort("H","D","A",,"B",1)
;Sort: First priority column H, descending, second priority column A ascending, third priority column B
$RC = $oXl.Range("A" + $SortStartRow).Select
$oXl.Visible = -1
$oXl = 0
Return

Function xlSort($ByCol1,Optional $Order1,Optional $ByCol2,Optional $Order2,Optional $ByCol3,Optional $Order3)
  ; Parameters:
  ; $ByCol1 : Columnletter for first sort priority
  ; $Order1 : "A", 1 or nothing for ascending sort
  ; "D" or 2 for descending sort
  ; $ByCol2 : Optional Columnletter for second sort priority
  ; $Order2 : See $Order1
  ; $ByCol3 : Optional Columnletter for third sort priority
  ; $Order3 : See $Order1
  ;
  ; Requires global parameters:
  ; $oXl ; COM handle to Excel
  ; $xlHeader = 1 ; Sheet has a header 1=Yes 2=No
  ; $SortStartRow ; "1" if no header else number of first datarow as Char: "2" or "3" or "4" or ..........

  $ParmOk = 1
  For $i = 1 To 3 ; Parameter check
    $RC = Execute("
    If '' + $$Order$i = ''
      $$Order$i = 1 ; Default sortorder, ascending
    Else
      Select
        Case $$Order$i = 'A'
          $$Order$i = 1
        Case $$Order$i = 'D'
          $$Order$i = 2
        Case $$Order$i <> 1 And $$Order$i <> 2 ; Illegal parameter
          $$ParmOk = 0
      EndSelect
    EndIf
    $$ByCol$i = $$ByCol$i + '$SortStartRow'
    ") ; End Execute
    If Not $ParmOk
      Return
    EndIf
  Next
  $RC = $oXl.Cells.Select
  If $ByCol2 <> $SortStartRow
    If $ByCol3 <> $SortStartRow
      $RC = $oXl.Selection.Sort($oXl.ActiveSheet.Range("$ByCol1"),$Order1,
      $oXl.ActiveSheet.Range("$ByCol2"),,$Order2,
      $oXl.ActiveSheet.Range("$ByCol3"),$Order3,$xlHeader)
    Else
      $RC = $oXl.Selection.Sort($oXl.ActiveSheet.Range("$ByCol1"),$Order1,
      $oXl.ActiveSheet.Range("$ByCol2"),,$Order2,,,$xlHeader)
    EndIf
  Else
    $RC = $oXl.Selection.Sort($oXl.ActiveSheet.Range("$ByCol1"),$Order1,,,,,,$xlHeader)
  EndIf
EndFunction


-Erik