Board index » delphi » Small Samples of combining Excel ranges or Selection.Areas

Small Samples of combining Excel ranges or Selection.Areas

Dear,

I try to speed up my interface to excell, and for this purpose I need to
combine ranges and set their properties all at once. For some of the manners
to do it, I can not get output, and I have specific questions

1) I want to use e.g. the Selection object

- ExcelWorksheet.Select adds the current range to the selection: RIGHT?
- ExcelApplication.Selection[LCID] returns me the selection object: it would
be helpfull, but I cannot threat it as a range: or : What can I do with it
and how can I do it: e.g. Setting the borders of it? Can you type a small
sample below?

2) Does something exists like Range_1 := Range_first + Range_Second;   //
Range_1, Range_First and Range_Second would be of type Range here..

Many thanks for saving my life. My boss will chase me and find me , even in
Syberia, if I do not show progress...

Jurgen De Block

 

Re:Small Samples of combining Excel ranges or Selection.Areas


<<Jurgen De Block:
1) I want to use e.g. the Selection object

Quote

Rule 1 of Office automation: if you want speed, avoid the Selection
object. :)

<<Jurgen:
What can I do with it and how can I do it: e.g. Setting the borders
of it?

Quote

var
  TheRange: Range;
..
  WS.Evaluate('A1:B2,D1:F6').Select;
  TheRange := Excel.Selection[lcid] as Range;
  TheRange.Borders.Item[xlEdgeBottom].Linestyle :=  xlDouble;
  TheRange.Borders.Item[xlEdgeBottom].Color := clLime;

<<Jurgen:
2) Does something exists like Range_1 := Range_first + Range_Second;  

Quote

The Application.Union method:
  TheRange := Excel.Union(WS.Range['A1','B2'], WS.Range['D4','F8']);
This call is the equivalent of the Evaluate method in the example
above.

For completeness, here's an example showing how you can access the
different areas of a multi-area range:

  Area: Range;
..
  for AreaNo := 1 to TheRange.Areas.Count do
  begin
    Area := TheRange.Areas[AreaNo];

    { put some data in the area }
    for i := 1 to Area.Count do
      Area.Item[i, EmptyParam].Value := i;

    { double the values in the second area and
      copy it 8 rows below itself }
    if AreaNo = 2 then
    begin
      ArrV := Area.Value;
      if VarArrayDimCount(ArrV) = 0 then
        // Area is a single cell
        WS.Range['A1', 'A1'].Value := ArrV * 2
      else
      begin
        for Row := 1 to VarArrayHighBound(ArrV, 1) do
          for Col := 1 to VarArrayHighBound(ArrV, 2) do
          begin
            Val := ArrV[Row, Col];
            ArrV[Row, Col] :=  Val * 2;
          end;
        Area.Offset[8, 0].Value := ArrV;
      end;
    end;
  end;

--
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:Small Samples of combining Excel ranges or Selection.Areas


<<Deborah Pate (TeamB):
  WS.Evaluate('A1:B2,D1:F6').Select;
  TheRange := Excel.Selection[lcid] as Range;

Quote

Please note that I put both these lines in because you
asked about the Selection specifically - it would be much
better in general to leae the selection stuff out:

  IDispatch(TheRange) := WS.Evaluate('A1:B2,D1:F6');

--
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:Small Samples of combining Excel ranges or Selection.Areas


Hey Deborah,

Hehe, I was thinking, does this support girl has a private life???

Many Many thanks for everything.
You helped me already hundreds of times. What can I damn do for you? ;)

Kind Regards, Jurgen

"Deborah Pate (TeamB)" <d.p...@blueyonder.co.not-this-bit.uk> wrote in
message news:VA.000010fe.005a115f@blueyonder.co.not-this-bit.uk...

Quote
> <<Jurgen De Block:
> 1) I want to use e.g. the Selection object

> Rule 1 of Office automation: if you want speed, avoid the Selection
> object. :)

> <<Jurgen:
> What can I do with it and how can I do it: e.g. Setting the borders
> of it?

> var
>   TheRange: Range;
> ..
>   WS.Evaluate('A1:B2,D1:F6').Select;
>   TheRange := Excel.Selection[lcid] as Range;
>   TheRange.Borders.Item[xlEdgeBottom].Linestyle :=  xlDouble;
>   TheRange.Borders.Item[xlEdgeBottom].Color := clLime;

> <<Jurgen:
> 2) Does something exists like Range_1 := Range_first + Range_Second;

> The Application.Union method:
>   TheRange := Excel.Union(WS.Range['A1','B2'], WS.Range['D4','F8']);
> This call is the equivalent of the Evaluate method in the example
> above.

> For completeness, here's an example showing how you can access the
> different areas of a multi-area range:

>   Area: Range;
> ..
>   for AreaNo := 1 to TheRange.Areas.Count do
>   begin
>     Area := TheRange.Areas[AreaNo];

>     { put some data in the area }
>     for i := 1 to Area.Count do
>       Area.Item[i, EmptyParam].Value := i;

>     { double the values in the second area and
>       copy it 8 rows below itself }
>     if AreaNo = 2 then
>     begin
>       ArrV := Area.Value;
>       if VarArrayDimCount(ArrV) = 0 then
>         // Area is a single cell
>         WS.Range['A1', 'A1'].Value := ArrV * 2
>       else
>       begin
>         for Row := 1 to VarArrayHighBound(ArrV, 1) do
>           for Col := 1 to VarArrayHighBound(ArrV, 2) do
>           begin
>             Val := ArrV[Row, Col];
>             ArrV[Row, Col] :=  Val * 2;
>           end;
>         Area.Offset[8, 0].Value := ArrV;
>       end;
>     end;
>   end;

> --
> 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:Small Samples of combining Excel ranges or Selection.Areas


<<Jurgen De Block:
What can I damn do for you? ;)

Quote

Lifelong devotion will do fine. ;)

--
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:Small Samples of combining Excel ranges or Selection.Areas


Jurgen,

I was wondering... why is here speed so important?
I know it always is but then again..

Koen

PS: regards to Lies and to your boss Pol !

"Jurgen De Block" <jdebl...@tecos.be> wrote in message
news:3c97050e$1_1@dnews...

Quote
> Dear,

> I try to speed up my interface to excell, and for this purpose I need to
> combine ranges and set their properties all at once. For some of the
manners
> to do it, I can not get output, and I have specific questions

> 1) I want to use e.g. the Selection object

> - ExcelWorksheet.Select adds the current range to the selection: RIGHT?
> - ExcelApplication.Selection[LCID] returns me the selection object: it
would
> be helpfull, but I cannot threat it as a range: or : What can I do with it
> and how can I do it: e.g. Setting the borders of it? Can you type a small
> sample below?

> 2) Does something exists like Range_1 := Range_first + Range_Second;   //
> Range_1, Range_First and Range_Second would be of type Range here..

> Many thanks for saving my life. My boss will chase me and find me , even
in
> Syberia, if I do not show progress...

> Jurgen De Block

Other Threads