#85335 - 2002-03-14 09:48 PM
Handling COM-objects inside COM-objects
|
kholm
Korg Regular
Registered: 2000-06-19
Posts: 714
Loc: Randers, Denmark
|
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]](images/icons/smile.gif) But fortunately for us, Ruud has made it possible to call objects within objects !!!
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
|
|
Top
|
|
|
|
|
#85337 - 2002-03-14 10:52 PM
Re: Handling COM-objects inside COM-objects
|
kholm
Korg Regular
Registered: 2000-06-19
Posts: 714
Loc: Randers, Denmark
|
Shawn,
No problem any more.
It was new to me that the caller should do all the object handling, and furthermore that KiX can handle it.
You should have let your post stay on the board, it would have spared me some time of syntax checking in KiX and Excel ![[Wink]](images/icons/wink.gif)
-Erik
|
|
Top
|
|
|
|
Moderator: Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart
|
0 registered
and 2312 anonymous users online.
|
|
|
