Sort by column in Excel from Excel Buffer

dn · 2007-05-25

I'm using an excel buffer now. And want to sort data in column "L4".
Anyone who now how I can manage it with Excel Buffer.?
I found there is Xlrange..but how do I use it??
:-k

Arhontis · 2007-05-25

By searching the forum about excel sort I found some nice info:
http://www.mibuso.com/forum/viewtopic.p ... excel+sort
http://www.mibuso.com/forum/viewtopic.p ... excel+sort

For example to sort by column B try:
XlWorkSheet.Range('A6:AD600').Sort(XlWorkSheet.Range('B2'));

I can't help you exactly on where to put the code to sort the column in the excel buffer table, but I think at the end of the routine CreateSheet, like:

Function CreateSheet.....
    .
    .
    .
    XlWrkSht.Range('A' + FORMAT(1) + ':' + xlColID + xlRowID).Columns.AutoFit;
    XlWrkSht.Range('A' + FORMAT(1) + ':' + xlColID + xlRowID).Sort(XlWrkSht.Range('L4'));
  END;
END;
Window.CLOSE;

It might do the trick...

dn · 2007-05-25

nice..I'll try...thx

dn · 2007-05-25

:-k ..hm the problem is I have headline on row above.
If i use
XlWrkSht.Range('A' + FORMAT(1) + ':' + xlColID + xlRowID).Sort(XlWrkSht.Range('L4'));

Then the content in Excel look no good

Arhontis · 2007-05-25

What do you mean look no good? The headers went to the bottom?

Experiment a little with the parameters about the xlColID + xlRowID and the XlWrkSht.Range('L4')...

The whole concept is to select all the data you have and sort them at L4...
Is the L1,L2,L3 headers? If yes then you could try freeze panes before sorting, so that the headings stay at the top...
Or choose 'A'+FORMAT(4) if your first 3 lines are headers...

Anyway, practice makes perfect...

dn · 2007-05-25

Yes..Line 3 is headline..
I've tried with

XlApp.Range('A3').Select; //...
XlApp.ActiveWindow.FreezePanes := TRUE;
XlWrkSht.Range('A' + FORMAT(4) + ':' + xlColID + xlRowID).Sort(XlWrkSht.Range('L4'));

but the result is still odd

.. #-o

Arhontis · 2007-05-25

Hmmm....
What do you mean odd?
Where do you export to excel from?
Analysis By Dimensions?

dn · 2007-05-25

Hi
It's a new report I created to export invoices to Excel.
I want to sort Margin in percent in Column L4 via Excel Buffer

Arhontis · 2007-05-25

Hi...

Which report did you consult to make our own? I think Report 113 is a very nice example to use to make your own report export to excel.

I tested the following code with report 113 (Customer/Item) and it worked very well...

Function CreateSheet
  .
  .
  .
  UNTIL NEXT = 0;
  XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
  XlWrkSht.Range('A2').Select;//added this line
  XlApp.ActiveWindow.FreezePanes := TRUE;//added this line
  XlWrkSht.Range('A' + FORMAT(2) + ':N65000').Sort(XlWrkSht.Range('D2'));//added this line
END;

IF UseInfoSheed THEN BEGIN
  IF InfoExcelBuf.FIND('-') THEN BEGIN
    XlWrkSht := XlWrkBk.Worksheets.Add();
.
.
.

To make it better you must make a new function in excel buffer table with name like:

Function SortColumn(StartColumn:code;StartRow:Integer;EndColumn:Code;EndRow:Integer;SortColumn:Code);
BEGIN
  XlWrkSht.Range(StartColumn+FORMAT(StartRow-1)).Select;
  XlApp.ActiveWindow.FreezePanes := TRUE;
  XlWrkSht.Range(StartColumn+FORMAT(StartRow) + ':'+EndColumn+FORMAT(EndRow-1)).Sort(XlWrkSht.Range(SortColumn+FORMAT(StartRow)));
END;

And in your report, after calling the CreateSheet and before the GiveUserControl you should execute something like:
SortColumn('A',2,'Z',65000,'B');
to freeze panes and sort the column B from row 2 and on...

I suggest you study the report 113 on how it exports to excel... It is all in routines...

dn · 2007-05-25

thx u...

Prajeesh_Nair · 2013-01-17

Hi Arhontis,

The code works fine, how i can sort it in ascending order in excel.
Any one ?

Arhontis · 2013-01-17

Try something like:

XlWorkSheet.Range('A6:AD600').Sort(XlWorkSheet.Range('B2'));

Prajeesh_Nair · 2013-01-17

Hi Arhontis,
I have created a function in excel buffer like you said. The sorting is working perfectly =D> . The sorting is done in ascending order.
But i need the sorting order in Descending order. i have to create one more variable in the function with which i can tell the function to make the sorting order (xlDescending. Sorts Key1 in descending order. ) How can i achieve this please give your suggestion.

chi · 2013-01-19

Hi

Try to use the following code properly in your function:
XlWorkSheet.Range('A6:AD600').Sort(XlWorkSheet.Range('B2'),1); // Sorting in ascending order.
XlWorkSheet.Range('A6:AD600').Sort(XlWorkSheet.Range('B2'),2); // Sorting in descending order.

Best regards

Prajeesh_Nair · 2013-01-21

Thanks Chi,

It worked i have added one more variable in my function which will decide the Sort order. Thanks very much for your valuable replay.

SortColumn(StartColumn : Code[10];StartRow : Integer;EndColumn : Code[10];EndRow : Integer;SortColumn : Code[10];SortOrder : Integer)
BEGIN
XlWrkSht.Range(StartColumn+FORMAT(StartRow-1)).Select;
XlApp.ActiveWindow.FreezePanes := TRUE;
XlWrkSht.Range(StartColumn+FORMAT(StartRow) + ':'+EndColumn+FORMAT(EndRow-1)).Sort(XlWrkSht.Range(SortColumn+FORMAT(StartRow)),SortOrder)
END;

Howdy, Stranger!

Quick Links

Categories

Sort by column in Excel from Excel Buffer

Comments