CODEWITHSUNDEEP
exceldelphi
user1009073
user1009073

Reputation: 3238

Delphi - Copying Rows in Excel

Delphi Tokyo - I have a Delphi app which is working against an Excel spreadsheet. I have a source sheet... called RawData. I need to copy specific rows (based on a filter) to another worksheet in the same workbook (called ActivitySheet). While I can get this to work... I am getting an Excel error message at the end of the process with the message 'The picture is too large and will be truncated.' This makes me believe that the command I am using is using the Clipboard, so I don't want to do that.

Here is what I am doing now...

var
  ActivitySheet : _WorkSheet;
  destRange : OleVariant;
begin   

  oExcel.Worksheets.Add(EmptyParam, EmptyParam, 1, xlWorksheet, LOCALE_USER_DEFAULT);
  ActivitySheet:= oExcel.ActiveSheet as _Worksheet;
  ActivitySheet.Name := 'Activity';
  destRange := ActivitySheet.Range['A1', 'A1'];

  // Set RawData Sheet as active
  RawDataSheet.Activate(LOCALE_USER_DEFAULT);
  Application.ProcessMessages;


  // Now add the both filters that we want... (Not real filters, but easier to understand)
  RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].AutoFilter(1, '=*Blue*', xlOr, '=*Red*', True );
  RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].AutoFilter(2, '=Car', xlOr, '=Truck', True );

  ShowMessage(IntToStr(RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].Rows.Count));
  RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].Copy(destRange);
  ...

While this code works, and only copies the rows that are valid based on the current filter, it gives me the error at the 'Save Spreadsheet' stage later in my code. I thought about using a Vararray, but the ShowMessage line shows me the TOTAL rows in RawSheet, not the Filtered rows...so I would have to iterate through all the rows in the sheet (as opposed to just filtered rows) and then determine if the row is valid...

What is the best way to copy a set of filtered rows to a new sheet?

Upvotes: 0

Views: 1136

Answers (1)

MartynA
MartynA

Reputation: 30715

When I first saw this q, I had no idea how to do what you are asking. After a number of false starts, I came across this answer

Excel Filtering and Copying in VBA

which shows that it can be done very simply, if you know the exact "magic spell" to do it.

Here is the code I wrote. I have used late binding to Excel throughout because it avoids having to spray references to LOCALE_USER_DEFAULT everywhere.

type
  TDefaultForm = class(TForm)
    [...]
  public
    vExcel,
    vWB,
    vRange,
    vSheet,
    vActivitySheet,
    vRawDataSheet : OleVariant;
  end;
[...]
  procedure TDefaultForm.CopyFilteredRange;
  var
    i : Integer;
    vRange : OleVariant;
    vDestRange : OleVariant;
  begin
    vRange := vRawDataSheet.Range['A1', 'A100'];
    vDestRange := vActivitySheet.Range['A1', 'A1'];
    vRange.SpecialCells(xlCellTypeVisible).Copy(vDestRange);
  end;

  procedure TDefaultForm.TestFilter;
  var
    vRange : OleVariant;
  begin
    vExcel := CreateOleObject('Excel.Application');
    vExcel.Visible := True;

    vWB := vExcel.WorkBooks.Add;

    vRawDataSheet := vWB.ActiveSheet;
    vActivitySheet := vWB.Sheets[2];

    vRange := vRawDataSheet.Range['A1', 'A100'];

    vRange.Item[1, 1]  := 'Vehicle';
    vRange.Item[2, 1]  := 'Car';
    vRange.Item[3, 1]  := 'Truck';
    vRange.Item[4, 1]  := 'Truck';
    vRange.Item[5, 1]  := 'Car';
    vRange.Item[6, 1]  := 'Truck';
    vRange.Item[7, 1]  := 'Truck';

    vRawDataSheet.UsedRange.Select;

    vRawDataSheet.UsedRange.AutoFilter(Field := 1, Criteria1 := 'Car');
    CopyFilteredRange;
  end;

Btw, in case you are not familiar with it, the way of passing the arguments in the call

   vRawDataSheet.UsedRange.AutoFilter(Field := 1, Criteria1 := 'Car');

is a special Delphi syntax for late-binding which avoids the rigmarole of passing all the arguments required for early binding.

Also btw, I got the "Picture too large ..." message, but it seems easy to avoid: just include ClipBrd in the Uses list, and call ClipBoard.Clear after copying the filtered rows. Ordinarily, I don't like messing with the Clipboard because it's a system-wide resource, but as a fast fix here, clearing it may be acceptable.

Upvotes: 2

Related Questions