Strip Formulas, Just Leave Values

[bcrooker]

I need to essentially copy the first sheet of a multi-sheet workbook to a new sheet and just take along the values, not formulas. Then delete all of the previous sheets.

So the workbook has sheets A, B and C. A shows summary calculations based on B and C. I want to add sheet D, copy the values from A to it and then delete sheets A, B and C.

My end result is that I want to have an XLS file that only has the Summary sheet.

I tried this but the formulas seem to be coming along:

XLS.Sheets.Add;

XLS.CopyCells(0, 0, 0, 100, 100, XLS.Sheets.Count - 1, 0, 0, [ccoCopyValues]);

while XLS.Sheets.count > 1 do
XLS.Sheets.Delete(0);

XLS.WriteToStream(aSummaryReportData);

[larsa]

Hello

CopyCells copies all cells, including formulas. If you want to strip formulas, you can use a code like this:

uses XLSReadWriteII4, SheetData4, CellFormats4, Cell4;

procedure TForm1.Button1Click(Sender: TObject);
var
Col,Row: integer;
begin
FXLS.Sheet[0].CalcDimensions;

for Row := FXLS.Sheet[0].FirstRow to FXLS.Sheet[0].LastRow do begin
for Col := FXLS.Sheet[0].FirstCol to FXLS.Sheet[0].LastCol do begin
if FXLS.Sheet[0].Cell[Col,Row].CellType in [ctNumberFormula,ctStringFormula,
ctBooleanFormula,ctErrorFormula,ctNumberArrayFormula,ctStringArrayFormula]
then
FXLS.Sheet[0].DeleteCell(Col,Row);
end;
end;
end;