How can I access a range of rows in Excel?

Question

I am trying to translate this Excel VBA code to Delphi:

ActiveSheet.Rows(r & ":5000").WrapText = True
ActiveSheet.Rows(r & ":5000").AutoFit

However in the Excel2010 unit in Delphi _Worksheet.Rows is an object, not a function or an array object, I also can't find any Items property or similar.

uses
  Excel2010;

procedure Test;
var
  Sheet: ExcelWorksheet;
  R: Integer;
begin
  R := 3;
  Sheet.Rows[R.ToString + ':5000'].WrapText := True;
  // Sheet.Rows.WrapText := True;
end;

The compiler message is:

[dcc32 Error] Unit1.pas(110): E2149 Class does not have a default property

What is the correct translation of the VBA code?

How can I access a certain range of Rows in Excel?

Answer 1

The thing is, if you work with Excel from Delphi using early binding (e.g. using CoExcelApplication.Create), you are working with the raw interfaces Excel exposes, rather than the variants you get working with late binding (using CreateOleObject('Excel.Application')).

Both methods have their strengths, early binding is best for speed and taking advantage of Delphi's type-checking and code completion, whereas late binding is useful for avoiding to have to specify all the arguments of the methods which have a lot of them.

In Excel_Tlb (or whatever your Excel import unit is called), maybe the best way to think of the Rows member of the _Worksheet interface is as a function which returns a dispinterface to an ExcelRange object. The members which return an ExcelRange interface take two arguments, which specify the top left and bottom right cells defining the range. So, one way to do something along the lines you've asked about is the following:

That's how you get at and use the Item property you were wondering about.

procedure TDefaultForm.TestRange;
var
  WB : _Workbook;
  vWB,
  vRange,
  vSheet : OleVariant;
  Sheet: _Worksheet;
  Range :  ExcelRange;
begin
  lcid := LOCALE_USER_DEFAULT;
  Excel := CoExcelApplication.Create;
  Excel.Visible[LOCALE_USER_DEFAULT] := True;

  WB := Excel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
  Sheet := WB.ActiveSheet as ExcelWorksheet;

  Range := Sheet.Range['A1', 'B2'];
  Range.RowHeight := 33;

  Range.Item[1, 1]  := 'some text long enough to wrap';
  Range.Item[1, 1].WrapText := True;

  Range.Item[1, 2]  := 'more text long enough to wrap';
  Range.Item[2, 2]  := 'other text long enough to wrap';


  //  The following shows how to use the special syntax for passing arguments
  //  in late-binding

  Excel.DisplayAlerts[LOCALE_USER_DEFAULT] := False;  //  suppresses  "Overwrite?" prompt if file already exists
  vWB := WB;
  //  Compare the following with what you would need if you called WB.SaveAs()
  vWB.SaveAs(FileName := ExtractFilePath(Application.ExeName) + 'Test.Xlsx');

  //  some things using late binding
  vSheet := Sheet;
  vRange := vSheet.Range['c3'];
  vRange.Value := 'some value';

  vRange := vSheet.Range['d3:e4'];
  vRange.Value := 'another value';

  //  retrieve the ExcelRange object from the vRange variant
  Range := IDispatch(vRange) as ExcelRange;

end;

Answer 2

With early binding, selecting A column (rows 3..5000) and using EntireRow, for example like this:

uses Excel2010;

procedure TForm14.Button1Click(Sender: TObject);
var
  Excel: ExcelApplication;
  Wbook: ExcelWorkbook;
  Sheet: ExcelWorksheet;
begin
  Excel := CoExcelApplication.Create;
  Wbook := Excel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
  Sheet := Wbook.ActiveSheet as ExcelWorksheet;

  Excel.Visible[LOCALE_USER_DEFAULT] := True;

  Sheet.Range['A3','A5000'].EntireRow.WrapText := True;
  Sheet.Range['A3','A5000'].EntireRow.AutoFit;

  //...
end;

Answer 3

Here is an example of how to use an ExcelRange

var
  lcid: Integer;
  Range: ExcelRange;
  Worksheet: _WorkSheet;
  Row: Integer;
begin
  lcid := LOCALE_USER_DEFAULT;
  Wbk := ExcelApplication1.Workbooks.Open(Filename,
      EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,lcid);

  WorkSheet := Wbk.Worksheets.Item['Sheet1'] as _Worksheet;
  WorkSheet.Activate(lcid);
  Row := 1;
  Range := WorkSheet.Range['A'+IntToStr(row), 'F'+IntToStr(row)];
  Range.Insert(xlShiftDown, xlFormatFromLeftOrAbove); // shift down and copy
  WorkSheet.Cells.Item[row, 6] := edtVatRate.Value;
end;