none
How would I set a text in an excel textbox using C#

    Question

  • Hello!

    I create an excel textbox(Drawing) dynamically through my application, which is written in C#. My question is how would I add text to it! This is my code for adding the textbox.

    Globals.SheetMain.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, iLeft, iTop, iWidth, iHeight);
    I am using VS 2005 and my version of excel is 2003!
    Thanks for your time!
    Wednesday, June 27, 2007 7:22 PM

Answers

  • Hi,

    Strange, I just create a new VSTO Excel workbook project. And use the following codes in Sheet1_Startup:

    Code Snippet

    Excel.Shape textBox = this.Shapes.AddTextbox(Office.MsoTextOrientation.msoTextOrientationHorizontal,20,20,100,100);

    textBox.TextFrame.Characters(missing,missing).Text = "Hi";

     It works fine in my site.

    "Cannot set the text property" is a runtime error or a building time error?

    Which are you working with, VSTO or Office Automation?

     

     

    Thanks

    Ji

    Friday, June 29, 2007 1:58 AM
  •  sagittarian wrote:
    Thanks Ji Zhou for the response. But I am still having problems. I am reproducing a part of my code here:

    foreach (DataRow myDataRow in ds.Tables["Result"].Rows)
    {
                        Shape textBox = Globals.Sheet1.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, iLeft + iWidth+10, Top, 60, 10);
                        textBox.TextFrame.Characters(System.Type.Missing, System.Type.Missing).Text = myDataRow[1].ToString();
                    }
    I see a textBox being created.

    This piece of code is inside a button event handler and that button is in my ActionsPane. And the exact exception.Message is,
    Unable to set the Text Property of the Characters class. I am confused :-(

     

    The core part of your code (the two statements that create and populate the textbox) works here. If you create a "quicky" WinForms project and use the following, do you still see the problem

     

            private void button1_Click(object sender, EventArgs e)
            {
                xl.Application xlApp = new xl.Application();
                xl.Workbook wb = xlApp.Workbooks.Add(System.Type.Missing);
                xl.Worksheet ws = (xl.Worksheet)wb.Sheets[1];
                xlApp.Visible = true;
                xl.Shape textBox = ws.Shapes.AddTextbox(
                    Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal,
                    10 + 10 + 10, 36, 60, 10);
                textBox.TextFrame.Characters(System.Type.Missing, System.Type.Missing).Text
                    = "testing";
                xlApp.ActiveWindow.Activate();
                xlApp.UserControl = true;
                ws = null;
                wb = null;
                xlApp = null;
            }

     

    If you're still getting the problem, can you test on a different machine? You might also try Help/Detect and Repair in the Excel application interface.

    Saturday, June 30, 2007 4:36 PM

All replies

  • Try this:

     

    Microsoft.Office.Interop.Excel.Shape shape;

    shape = Globals.Sheet1.Shapes.AddTextbox

    (Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal,

    20, 20, 100, 100);

    shape.TextFrame2.TextRange.Text = "VSTO";

     

     

    Wednesday, June 27, 2007 10:44 PM
  • Thanks for the response Ian.

    But the TextFrame2 property am afraid is not there in 2003/VSTO 2005. I see that its there in the 2007 version. Am still waiting for someone to help me. Thanks in advance!
    Wednesday, June 27, 2007 11:35 PM
  • Try it with TextFrame.TextRange (no 2). Office 2003 should have TextFrame.TextRange for the Shape object.
    Thursday, June 28, 2007 10:13 AM
  • Appreciate your response Cindy. But it ain't working! Looks like the TextFrame.TextRange property is not there in excel textboxes.

    I tried shape.TextFrame.Characters(missing, missing).Text = "Something";

    But it says, cannot set the text property!
    Thursday, June 28, 2007 3:36 PM
  • How about

     

    shape.TextFrame.TEXTRANGE.Text = "something";

    Thursday, June 28, 2007 4:16 PM
  • Hi,

    Strange, I just create a new VSTO Excel workbook project. And use the following codes in Sheet1_Startup:

    Code Snippet

    Excel.Shape textBox = this.Shapes.AddTextbox(Office.MsoTextOrientation.msoTextOrientationHorizontal,20,20,100,100);

    textBox.TextFrame.Characters(missing,missing).Text = "Hi";

     It works fine in my site.

    "Cannot set the text property" is a runtime error or a building time error?

    Which are you working with, VSTO or Office Automation?

     

     

    Thanks

    Ji

    Friday, June 29, 2007 1:58 AM
  • Hi again,

    I also trace the Characters interface of TextFrame in object view. Look at this:

    Code Snippet

        public interface Characters

        {

            [DispId(148)]

            Application Application { get; }

            [DispId(139)]

            string Caption { get; set; }

            [DispId(118)]

            int Count { get; }

            [DispId(149)]

            XlCreator Creator { get; }

            [DispId(146)]

            Font Font { get; }

            [DispId(150)]

            object Parent { get; }

            [DispId(1522)]

            string PhoneticCharacters { get; set; }

            [DispId(138)]

            string Text { get; set; }

     

            [DispId(117)]

            object Delete();

            [DispId(252)]

            object Insert(string String);

        }

     

    The Text property is a get&set property. It should be able to be modified. Try to clean your build and rebuild the project :-)

     

    Friday, June 29, 2007 2:21 AM
  • Thanks Ji Zhou for the response. But I am still having problems. I am reproducing a part of my code here:

    foreach (DataRow myDataRow in ds.Tables["Result"].Rows)
    {
                        Shape textBox = Globals.Sheet1.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, iLeft + iWidth+10, Top, 60, 10);
                        textBox.TextFrame.Characters(System.Type.Missing, System.Type.Missing).Text = myDataRow[1].ToString();
                    }
    I see a textBox being created.

    This piece of code is inside a button event handler and that button is in my ActionsPane. And the exact exception.Message is,
    Unable to set the Text Property of the Characters class. I am confused :-(
    Friday, June 29, 2007 7:46 PM
  •  sagittarian wrote:
    Thanks Ji Zhou for the response. But I am still having problems. I am reproducing a part of my code here:

    foreach (DataRow myDataRow in ds.Tables["Result"].Rows)
    {
                        Shape textBox = Globals.Sheet1.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, iLeft + iWidth+10, Top, 60, 10);
                        textBox.TextFrame.Characters(System.Type.Missing, System.Type.Missing).Text = myDataRow[1].ToString();
                    }
    I see a textBox being created.

    This piece of code is inside a button event handler and that button is in my ActionsPane. And the exact exception.Message is,
    Unable to set the Text Property of the Characters class. I am confused :-(

     

    The core part of your code (the two statements that create and populate the textbox) works here. If you create a "quicky" WinForms project and use the following, do you still see the problem

     

            private void button1_Click(object sender, EventArgs e)
            {
                xl.Application xlApp = new xl.Application();
                xl.Workbook wb = xlApp.Workbooks.Add(System.Type.Missing);
                xl.Worksheet ws = (xl.Worksheet)wb.Sheets[1];
                xlApp.Visible = true;
                xl.Shape textBox = ws.Shapes.AddTextbox(
                    Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal,
                    10 + 10 + 10, 36, 60, 10);
                textBox.TextFrame.Characters(System.Type.Missing, System.Type.Missing).Text
                    = "testing";
                xlApp.ActiveWindow.Activate();
                xlApp.UserControl = true;
                ws = null;
                wb = null;
                xlApp = null;
            }

     

    If you're still getting the problem, can you test on a different machine? You might also try Help/Detect and Repair in the Excel application interface.

    Saturday, June 30, 2007 4:36 PM
  • What is the length of the text you are adding?  I noticed with Automation there is a limit of 256 characters.
    Wednesday, May 27, 2009 11:29 PM