none
Sending Data from Windows Form to Excel RRS feed

  • Question

  • I am new to VSTO.  I followed a totorial to collect data using a windows form

    http://msdn.microsoft.com/en-ca/library/vstudio/f1wckde0.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-3

    I succeeded in sending data to a single cell, but I'm wondering how i would go upon sending data to several cells through text box's using using 1 button 

    Tuesday, March 5, 2013 8:40 PM

Answers

  • Hi Jordan8,

    My code snippet was just an example on how to address ranges. You simply select your range/cell and put in the form value from the text boxes in the form just like you did in the original example with only the difference that now you specify your range by directly accessing it instead of using a "Name Range"

    wks.Range["A1"].Value2 = "Bla1";
    wks.Range["A2"].Value2 = "Bla2";
    wks.Range["A3"].Value2 = "Bla3"; 

    Instead of "Bla" you obviously put the reference to your textboxes

    wks.Range["A1"].Value2 = this.textBox1.Text;
    wks.Range["A2"].Value2 = this.textBox2.Text;
    wks.Range["A3"].Value2 = this.textBox3.Text;

    Hope this helps,

    -= Maarten =-


    Software Engineer * MVP-Visual Developer-VSTO

    Thursday, March 7, 2013 8:27 AM
    Moderator
  • Hi Jordan,

    Sorry about the late response.

    Please try the following code:

        public partial class GetInputString : Form
        {
            public GetInputString()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Globals.ThisWorkbook.WriteStringToCell("A1",this.textBox1.Text);
                Globals.ThisWorkbook.WriteStringToCell("B1", this.textBox2.Text);
                Globals.ThisWorkbook.WriteStringToCell("C1", this.textBox3.Text);
                this.Dispose();
            }
        }

            // in ThisWorkbook class.
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                this.Open += new Excel.WorkbookEvents_OpenEventHandler(ThisWorkbook_Open);
            }
    
            void ThisWorkbook_Open()
            {
                GetInputString inputForm = new GetInputString();
                inputForm.Show();
            }
    
            public void WriteStringToCell(string loc, string formData)
            {
                Globals.Sheet1.Range[loc].Value2 = formData;
            }
    

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 14, 2013 5:31 AM
    Moderator

All replies

  • Jordan8

    It depends, there are several ways to accomplish what you'd like to see. In the example the range filled with the information you entered in the text box was sent to a Named Range. You could do something similar by direct accessing the cells in your workbook by addressing the specific Range in your sheet. For example:

    C#

    Excel.Worksheet wks = this.ActiveSheet as Excel.Worksheet;
    wks.Range["A1:B3"].Value2 = "Bla"; 

    This will set the text on the Range of Cell A1 to B3 with the same value ("Bla").

    Obviously you can set each individual Range with it's own value when you click your button in your button handler.

    Hope this helps,

    -= Maarten =-


    Software Engineer * MVP-Visual Developer-VSTO


    Wednesday, March 6, 2013 12:46 AM
    Moderator
  • Thank you for your reply Maartin.

    That code still doesn't resolve the problem that I'm encountering. What would the code be to associate each individual textbox on my form with an individual cell in excel. With this code u provided it only allows me to place the same text in all the cells from 1 textbox. I have  many textboxes and listboxes in my form and i would like each one to send data to a specific cell. 
    Wednesday, March 6, 2013 3:43 PM
  • Hi Jordan8,

    My code snippet was just an example on how to address ranges. You simply select your range/cell and put in the form value from the text boxes in the form just like you did in the original example with only the difference that now you specify your range by directly accessing it instead of using a "Name Range"

    wks.Range["A1"].Value2 = "Bla1";
    wks.Range["A2"].Value2 = "Bla2";
    wks.Range["A3"].Value2 = "Bla3"; 

    Instead of "Bla" you obviously put the reference to your textboxes

    wks.Range["A1"].Value2 = this.textBox1.Text;
    wks.Range["A2"].Value2 = this.textBox2.Text;
    wks.Range["A3"].Value2 = this.textBox3.Text;

    Hope this helps,

    -= Maarten =-


    Software Engineer * MVP-Visual Developer-VSTO

    Thursday, March 7, 2013 8:27 AM
    Moderator
  • Hello Maarten, 

    when I use the code...

    wks.Range["A1"].Value2 = "Bla1";

    I it works with no errors, but i am trying to send text from a textbox. so I use..

    wks.Range["A1"].Value2 = this.textBox1.Text;
    but i get the error..

    Error 1 'ExcelWorkbook1.ThisWorkbook' does not contain a definition for 'textBox1' 

    here is what my code looks like....
            public void WriteStringToCell(string formData) - this is under ThisWorkbook.cs
            {
                Excel.Worksheet wks = this.ActiveSheet as Excel.Worksheet;
                wks.Range["A1"].Value2 = this.textBox1.Text;
    
            private void button1_Click(object sender, EventArgs e)- this us the event handler for the button
            {
                Globals.ThisWorkbook.WriteStringToCell(this.textBox1.Text);
                this.Dispose();
            }


    I am clearly doing something wrong but since i an very new and "self taught it is verry difficult for me to find my problem.

    I greatly appreciate you taking the time to help me

    regards,

    Jordan
    Monday, March 11, 2013 12:41 PM
  • Hi Jordan,

    Sorry about the late response.

    Please try the following code:

        public partial class GetInputString : Form
        {
            public GetInputString()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Globals.ThisWorkbook.WriteStringToCell("A1",this.textBox1.Text);
                Globals.ThisWorkbook.WriteStringToCell("B1", this.textBox2.Text);
                Globals.ThisWorkbook.WriteStringToCell("C1", this.textBox3.Text);
                this.Dispose();
            }
        }

            // in ThisWorkbook class.
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                this.Open += new Excel.WorkbookEvents_OpenEventHandler(ThisWorkbook_Open);
            }
    
            void ThisWorkbook_Open()
            {
                GetInputString inputForm = new GetInputString();
                inputForm.Show();
            }
    
            public void WriteStringToCell(string loc, string formData)
            {
                Globals.Sheet1.Range[loc].Value2 = formData;
            }
    

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 14, 2013 5:31 AM
    Moderator