CODEWITHSUNDEEP
c#excelinterop
Aximili
Aximili

Reputation: 29474

C# and Microsoft.Office.Interop.Excel - How to replace a formula with its value?

I am trying to replace formulas on column D with their values.

eg. currently D1 = C1 / 2

If C1 = 10, I want D1 to be 5

I need to do this because I need to delete the column C.

I tried changing the format to Text like below but it doesn't seem to replace formulas with their values

  Excel.Style style = workbook.Styles.Add("styleText", Missing.Value);
  style.NumberFormat = "@";

  Excel.Range range = (Excel.Range)sheet.get_Range("D1", Missing.Value);
  range.Style = style;

Upvotes: 1

Views: 6346

Answers (3)

Aximili
Aximili

Reputation: 29474

How do you format a comment??

Here is the solution I got, thanks to Lorenzo

private static void ReplaceFormulasWithValues(ref Excel.Worksheet sheet, char column)
{
  Excel.Range range = (Excel.Range)sheet.get_Range(column + "1", Missing.Value).EntireColumn;
  range.Copy(Missing.Value);
  range.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
    Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}

This is another way to do it (C1 contains a formula, the result is placed in D1) 

static void Main( string[] args ) {
    string fileName = @"D:\devprj\Temp\TempProject\bin\Debug\Cartel1.xlsx";
    Application ac = new Application();
    Workbook wb = ac.Workbooks.Open( fileName );
    Worksheet ws = wb.Sheets[1];

    Range rangeOrigin = ws.get_Range( "C1" );
    Range rangeDestination = ws.get_Range( "D1" );
    rangeDestination.Value = rangeOrigin.Value2;

    wb.Save();
}

Upvotes: 1

Lorenzo
Lorenzo

Reputation: 29427

Here's a macro in VBA that does what you need... It's VB code but I dont think woould be a problem to translate it in C#

Sub ValuesOnly()
    Dim rRange As Range
    On Error Resume Next
    Set rRange = Application.InputBox(Prompt:="Select the formulas", Title:="VALUES ONLY", Type:=8)
    If rRange Is Nothing Then Exit Sub
    rRange = rRange.Value
End Sub

Another way to do it is to simply mimic the command Paste Special -> Values. I have just recorded a macro that does it (C5 in my sample is a cell that contains a function)

Sub Macro1()
    Range("C5").Select
    Selection.Copy
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Upvotes: 1

Andrew Cooper
Andrew Cooper

Reputation: 32576

You can't just change the display style, because that doesn't change the content of the cell. When you're doing this manually in Excel you need to copy the column and then choose Paste Special -> Values to paste the values rather than the formulae. I imagine there's a programatic way to do the same operation.

Upvotes: 0

Related Questions