Here is my take on it based on the above conversation. This will copy cells with no formula and will include all value + preserve all formating, validation, etc...
string safeExcelFilePath = @"[file path to workbooks goes here]";
string excelFile1 = Path.Combine(safeExcelFilePath, "Book1.xlsx");
string excelFile2 = Path.Combine(safeExcelFilePath, "Book2.xlsx");
_Application _excel = new _Excel.Application();
_excel.DisplayAlerts = false;
Workbook workbook1 = _excel.Workbooks.Open(excelFile1);
Workbook workbook2 = _excel.Workbooks.Open(excelFile2);
Worksheet worksheet1 = workbook1.Worksheets["Sheet1"];
Worksheet worksheet2 = workbook2.Worksheets["Sheet1"];
worksheet2.UsedRange.Delete(Type.Missing);
Range ranges = worksheet1.UsedRange.Cells.SpecialCells(XlCellType.xlCellTypeConstants);
foreach (object rangeObject in ranges)
{
Range srcRange = (Range)rangeObject;
srcRange.Copy(Type.Missing);
Range destRange = worksheet2.Cells[srcRange.Row, srcRange.Column];
destRange.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
}
workbook2.SaveAs(Filename: excelFile2, AccessMode: XlSaveAsAccessMode.xlNoChange);
workbook2.Close();
workbook1.Close();
_excel.Quit();
I left in the old code from the previous answer but commented out. Tested and works - all cells but cells with formula.
UPDATED - VERSION 2
As per the comments below, this will copy all, then remove the unwanted formula cells:
worksheet2.UsedRange.Delete(Type.Missing);
Range ranges = worksheet1.UsedRange.Cells.SpecialCells(XlCellType.xlCellTypeFormulas);
var count = ranges.Cells.Count;
worksheet1.UsedRange.Copy(Type.Missing);
worksheet2.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
Console.WriteLine("Cells to delete...");
foreach (object rangeObject in ranges)
{
Range srcRange = (Range)rangeObject;
Console.WriteLine($"{srcRange.Address} = [{srcRange.Row}, {srcRange.Column}]");
Range destRange = worksheet2.Cells[srcRange.Row, srcRange.Column];
destRange.Delete(Type.Missing);
}
workbook2.SaveAs(Filename: excelFile2, AccessMode: XlSaveAsAccessMode.xlNoChange);
I have 3,500 cells (14 x 250) with values only, and 15 formula cells. This only takes 190ms on my machine in debug mode vs over 5 minutes for the above copy each cell...