Sorting data in Excel OLE object form Lotusscript

[Roger Holme]

Hello

This seems a trivial problem, but I can't crack it.

I have created an Excel object in a Lotusscript library and passed some
data to it, I then select the appropriate cells and I am trying to sort
them by the first column.
I have tried lots of variations of
xlApp.Selection.Sort
xlApp.Sort
etc.
and the Lotusscript code simply stops at any of these lines.
If I switch to Excel and do "Sort" from the "Data" menu and accept the
defaults it sorts how I want.
I recorded a VB macro as follows:
Selection.sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
and this works from Excel.

Somehow I must be able to send this command from Lotusscript but can't
make it work.

Any help would be much appreciated.

Thanks
Roger Holme

[raweberg]

Get a handle to the App and selection then try:
With xlApp.Selection
.Sort xlSheet.Cells(1, 1), , , , , , , XLYES
End With

Also, I have a sub that I can call. Allowing me to sort by a selected
column (that the user has selected). It's called a UsedRange.
Excel97. Notice there are alot of nulls or commas if not being used.
Note: XLYES is a constant with a value of 1 (Const xlYes=1).
iSortyBy is an integer to represent the column.

Sub BuildxlWSSort
'Sort the Spreadsheet columns
If iSortBy <> 0 Then
'Parameters: (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
OrderCustom, MatchCase, Orientation, SortMethod)

Call xlSheet.UsedRange.Sort(xlSheet.Cells(1, iSortBy), , , , , , ,
XLYES)

End If

End Sub

Roger Holme <roger...@guardall.co.uk> wrote in message news:<a531pb$rud$1...@helle.btinternet.com>...

[hun]

Here's a sample from a Post on Notes.Net:

Set xlWorkbook = xlApp.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
Set XLSortRange = xlSheet.range("A"+Cstr(RRow)+":P"+Cstr(StatusRow))
Call XLSortRange.Sort(XLSortRange.Cells(2,1), 1)

The thread:
http://www.notes.net/46dom.nsf/55c38d716d632d9b8525689b005ba1c0/5d7600f2da1111fd85256a78005304bf?OpenDocument&Highlight=0,excel,macro,sort

Perhaps this one or something similar may assist you.

There are many other examples/threads on notes.net. Just do a search on
excel & macro & sort or, just excel & macro.
http://www.notes.net/46dom.nsf/DateAllThreadedweb?OpenView

Also, see some of the Posts from Doug Finner re: OLE automation. He had
created a FAQ.

If you look in the tab, References, within the Notes IDE Browser for
OLE/Excel Application, there's a method for executing macros:

Run(optional byval macro as variant, optional byval arg1 as variant,
optional byval arg2 as variant, optional byval arg3 as variant, optional
byval arg4 as variant, optional byval arg5 as variant, optional byval
arg6 as variant, optional byval arg7 as variant, optional byval arg8 as
variant, optional byval arg9 as variant, optional byval arg10 as
variant, optional byval arg11 as variant, optional byval arg12 as
variant, optional byval arg13 as variant, optional byval arg14 as
variant, optional byval arg15 as variant, optional byval arg16 as
variant, optional byval arg17 as variant, optional byval arg18 as
variant, optional byval arg19 as variant, optional byval arg20 as
variant, optional byval arg21 as variant, optional byval arg22 as
variant, optional byval arg23 as variant, optional byval arg24 as
variant, optional byval arg25 as variant, optional byval arg26 as
variant, optional byval arg27 as variant, optional byval arg28 as
variant, optional byval arg29 as variant, optional byval arg30 as

[W.Flamme]

"Roger Holme" <roger...@guardall.co.uk> schrieb im Newsbeitrag
news:a531pb$rud$1...@helle.btinternet.com...

> and the Lotusscript code simply stops at any of these lines.
> If I switch to Excel and do "Sort" from the "Data" menu and accept the
> defaults it sorts how I want.
> I recorded a VB macro as follows:
> Selection.sort Key1:=Range("A1"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> and this works from Excel.
>
> Somehow I must be able to send this command from Lotusscript but can't
> make it work.

Check out the Sort method from Excel VBA help:
Call xlRange.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,

Header, OrderCustom, MatchCase, Orientation, SortMethod)

Don't know your code but one reason might be OLE/COM doesn't know about
xlConstants when passing parameters (have a look at the VBA object
browser to find out their values), another reason is you can't pass sth.
like 'Range("A1")' because it must be passed as a valid Excel range
strName or xlRange object so it should be sth like "MyRange" or
xlApp.Worksheets("Sheet1").Range("A1:A5")

--

Wolfgang Flamme
wfl...@mainz-online.de

"I love deadlines. I love the whooshing sound they make as they fly by."
Douglas Adams

[Roger Holme]

Thanks to all who responded to my question. I have managed to fix this
problem, thanks to a combination of your responses.

Thanks again
Roger Holme

Message by rawe...@attbi.com (raweberg) on: 21/02/2002 10:31:09