Insert Formula to a shape object C# to Excel

Question

I have created a shape object in excel using C# and I want the text in a cell to appear in the shape. When I recorded the macro in excel, I got the below formula

ActiveSheet.Shapes.Range(Array("Rectangle1")).Select
        Selection.Formula = "=$D$21"

Can anyone help me on how to reproduce the above code in C#? Below is the C# code that controls excel

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication2
{
    public partial class ExcelTest : Form
    {

        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();


        public ExcelTest()
        {
            Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open("F:\\YourExcelSheet.xlsx");
            Microsoft.Office.Interop.Excel.Worksheet ws = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
            excel.Visible = true;
            Range MyCells = ws.Cells;
            MyCells.Item[1, "A"] = "Test";
            Microsoft.Office.Interop.Excel.Shape shp = ws.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 30, 100, 300, 100);
            shp.Name = "Shape1";
            shp.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = System.Drawing.Color.Gray.ToArgb();
            shp.Fill.ForeColor.RGB = System.Drawing.Color.White.ToArgb();
            shp.TextEffect.Text = Convert.ToString(MyCells.Item[1, "A"].Value);            
            //InitializeComponent();
        }
    }
}

Microsoft.Office.Interop.Excel.Shape Shp = ws.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 30, 100, 300, 100); I use this to add shape. If I use s1.TextEffect.Text ="Text"; i can get the text within the shape. But that's not required. i need to link it to a range — Jayanth, Mar 14 '17 at 18:30
btw, i think u are getting neg rep because you aren't showing enough code, like the c# that you already have. I would like to replicate what you are doing so that I can apply what I know, especially about Excel, into my version. But that's difficult without your code. — Bill Roberts, Mar 14 '17 at 18:42

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

Oh my - try this:

shp.DrawingObject.Formula = "=A1";

I found the answer here: Stack Overflow: Link cell with Excel Shape

edited May 23 '17 at 12:25

Community

1
1

answered Mar 15 '17 at 16:43

Bill Roberts

1,127
18
30

yes... errr no... no intellisense in c# for DrawingObject... and nor do you get intellisense in VBA (I tried both) - but in both places , successful compile and the formula set works. – Bill Roberts Mar 15 '17 at 16:49
No worries - I really dig c# and Excel. However, can you please mark this as answered? Thx kindly! :) – Bill Roberts Mar 16 '17 at 15:21

Insert Formula to a shape object C# to Excel

1 Answers1