Board index » delphi » Need help sorting Excel column using Range

Need help sorting Excel column using Range

What would be the correct syntax to use the

Range(Cells(1,4),Cells(10,17))

 from Delphi?  I need to use this in the Excel Sort Function.  I have
tried

excelsht := excelapp.Workbooks[1].Worksheet[1];
excelsht.Range[excelsht.Cells[2, AColumn], excelsht.Cells[ARow,
AColumn]].Sort(excelsht.Range[excelsht.Cells[2, AColumn],
excelsht.Cells[ARow, AColumn]],Order1 := 1,Header := 1);

where AColumn and ARow are integers tracking where I am in the spreadsheet

Thanks for any suggestions!

Leslie

 

Re:Need help sorting Excel column using Range


<<Leslie:
If you can point me in a better direction to accomplish
this with less processing, that would be great!

Quote

If you could post some code it would be easier to help. :)

--
Deborah Pate (TeamB) http://delphi-jedi.org

  TeamB don't see posts sent via Google or ISPs
  Use the real Borland server: newsgroups.borland.com
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Need help sorting Excel column using Range


I posted a thread titled: Excel Range(Cells(R1C1),Cells(R1C1)) that has
all the code.  Thanks for your help!

Leslie

Re:Need help sorting Excel column using Range


<<Leslie:
procedure AddMessage

Quote

You can use a variant array here rather than an array of
strings, since Excel can handle a variant array all at
once. You could also use offsets, but since all the text
appears in one column it would be better to get the column
name as a letter and write the data in one step, something
like this:

  messagetext : variant;
  ColLtr: string;
..
  MessageText := VarArrayCreate([0, 3, 0, 0], varOleStr);
  MessageText[0, 0] := 'This term begins...';
  MessageText[1, 0] := 'You must call the Jury Line...';
  MessageText[2, 0] := 'If you have any questions...';
  MessageText[3, 0] := 'Leanore Rios or Carmen ...';

  ColLtr := IntToXlCol(AColumn);
  WS.Range[ColLtr + IntToStr(ARow), ColLtr + IntToStr(
    ARow + VarArrayHighBound(MessageText, 1))].Value2 :=
MessageText;

<<Leslie:
procedure SortProcess

Quote

You refer to the same range here twice, using the same
rather long code to get at it (WS.Range[WS.Cells.Item[2,
AColumn], WS.Cells.Item[ARow, AColumn]]). In automation you
should always use a local variable to avoid doing that, it
cuts out a lot of slow interprocess calls. So this would be
a bit of an improvement

  Rng: OleVariant;
..
  Rng := WS.Range[WS.Cells.Item[2, AColumn],
WS.Cells.Item[ARow, AColumn]];
  Rng.Sort(Rng, Order1 := 1, Header := 2);

but it would again be better to get the column letter and
avoid using Cells, as in the example above.

<<Leslie:
procedure ProcessList

Quote

Here too you can use local variables to avoid making Excel
calls twice, for example for the excelsht.Cells[12, 1].Font
object. You might also, perhaps, be able to call Autofit
for all the columns in the range after everything else was
done, instead of in the loop as at present.

--
Deborah Pate (TeamB) http://delphi-jedi.org

  TeamB don't see posts sent via Google or ISPs
  Use the real Borland server: newsgroups.borland.com
  http://www.borland.com/newsgroups/genl_faqs.html

Other Threads