Excel Buffer - Deleting Rows

Jonathan2708

Hi,

I'm using the Excel Buffer to create a report in Excel and want to delete some rows that may have been created before sending to Excel. I had an issue with this so created a test app to examine. Look at the following code :

FOR intRowNumber := 1 TO 100 DO BEGIN
   FOR intColNumber := 1 TO 5 DO BEGIN
      tmptblExcelBuffer.INIT;
      tmptblExcelBuffer.VALIDATE("Row No.", intRowNumber);
      tmptblExcelBuffer.VALIDATE("Column No.", intColNumber);
      tmptblExcelBuffer."Cell Value as Text" := FORMAT(intRowNumber) + ' x ' + FORMAT(intColNumber);
      tmptblExcelBuffer.INSERT(TRUE);
   END;
END;

tmptblExcelBuffer.CreateBook();
tmptblExcelBuffer.CreateSheet('Excel Report 2', 'Excel Report 2', COMPANYNAME(), USERID());
tmptblExcelBuffer.GiveUserControl();

This code successfully creates an Excel sheet with 100 rows and 5 columns populated. Now say I want to delete row 50 before outputting to Excel - I add the following 3 lines immediately prior to the CreateBook() call :

tmptblExcelBuffer.RESET;
tmptblExcelBuffer.SETRANGE("Row No.", 50);
tmptblExcelBuffer.DELETEALL(TRUE);

When this is run the Excel sheet opens but with no cells populated at all. If I change the tmptblExcelBuffer variable to a non-temporary variable so I can see the rows created I see that all the rows are present except for row 50.

Any ideas what's happening here? Don't ask me why I don't just skip row 50 in the loop - the example is just to demonstrate this behaviour.

Thanks

Jonathan

Jonathan2708

Forget it, always answer my own questions after posting!

Obviously I needed to perform a tmptblExcelBuffer.RESET; before running the CreateBook() in order to clear the filter used for the delete.

Jonathan