Member Avatar for Jboctor8

I am trying to send Data from text boxes in my windows form to specific cells in excel. I am forgetting to do something because i keep getting this error...

Error 1 'ExcelWorkbook1.ThisWorkbook' does not contain a definition for 'textBox1' and no extension method 'textBox1' accepting a first argument of type 'ExcelWorkbook1.ThisWorkbook' could be found (are you missing a using directive or an assembly reference?)

This is the code I am trying to use in ThisWorkbook.cs.....

     public void WriteStringToCell (string formData)

            Excel.Worksheet wks = this.ActiveSheet as Excel.Worksheet;
            wks.Range["F3"].Value2 = this.textBox1.Text;
            wks.Range["E8"].Value2 = this.textBox2.Text;
            wks.Range["F8"].Value2 = this.listBox1.Text;

When i direct reference it right to excel like this...

wks.Range["F10"].Value2 = "Bla3";
            wks.Range["F11"].Value2 = "Bla3";
            wks.Range["F12"].Value2 = "Bla2";
            wks.Range["F13"].Value2 = "Bla3";

It Works Fine but obviously that's not what I'm looking to do. I need the data to go from the text boxes and list boxes on the windows form to excel.

This is what i have on the even handler for the button... (I want all data to be transferred with one button click)

private void button1_Click(object sender, EventArgs e)

            Globals.ThisWorkbook.WriteStringToCell(this.textBox1.Text);
            this.Dispose();

I know this is wrong and i have no idea what needs to go in this event handler. I am very new to VSTO as I'm sure u know by reading this. After saying that I ask that your answer is very straight forward as i have searched forums for days not finding help.

  • 3 Contributors
  • 7 Replies
  • 870 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JOSheaIV

Dani AI

Generated

Short answer: the compile error happens because ThisWorkbook is your Excel/VSTO workbook class — it does not contain the WinForms controls. You cannot write this.textBox1 inside ThisWorkbook unless that workbook class actually owns the control. The simple, correct pattern is to pass the values from the form into a public workbook method (or pass the form instance itself, but passing raw values keeps responsibilities separated).

Example (form side -> call a public method on the workbook):

// inside the WinForms button click
string a = textBox1.Text;
string b = textBox2.Text;
string sel = listBox1.SelectedItem?.ToString() ?? "";
Globals.ThisWorkbook.WriteFormValues(a, b, sel);
this.Close();
// inside ThisWorkbook (make sure the method is public)
public void WriteFormValues(string val1, string val2, string listVal)
{
    var sheet = (Microsoft.Office.Interop.Excel.Worksheet)this.Application.Worksheets["Sheet1"];
    sheet.Cells[3, 6].Value2 = val1;
    sheet.Cells[8, 5].Value2 = val2;
    sheet.Cells[8, 6].Value2 = listVal;
    this.Save(); // persist immediately if desired
}

Notes and troubleshooting:

  • Make the workbook method public and ensure the parameter list matches the call. Rebuild if the compiler still reports missing members.
  • As pointed out, Cells is convenient for numeric indexing and loops; using a named sheet (instead of ActiveSheet) avoids writing to the wrong sheet.
  • If the form is part of a separate EXE (not a VSTO add-in), do not call Globals.ThisWorkbook — use Interop to open the workbook from that process (as described) and be careful to call Marshal.ReleaseComObject and Quit() to avoid orphan EXCEL.EXE processes.
  • If you still see a save dialog on close, call this.Save() or save/close programmatically to suppress it.

This approach keeps UI code inside the form and workbook logic inside ThisWorkbook, which avoids the compile-time error and is easier to maintain.

Recommended Answers

All 7 Replies

Member Avatar for ddanbe

I was wondering why you created an extra variable wks because your Form appears to have an ActiveSheet field already.
I also prefer to use the Cells object instead of the Range object, because you can easy loop through it if needed. Cells, is in fact also a Range object.
This is one of my methods (worksheet is a private field)

public void PutData(int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;
        }
Edited by ddanbe because: add
Member Avatar for JOSheaIV

When it comes to Excel and C#, the only help I can offer is in Interop, as I have experience from using it. Not sure if this is what you are using, but I have provided pretty much all the code you'll need (you will have to add the reference "Microsoft.Office.Interop.Excel" to make this work). Maybe this is what you want to look into.

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

excelApp.Visible = false; //prevents us from seeing the file open
excelApp.ScreenUpdating = false;
excelApp.DisplayAlerts = false;

Workbook excelFile = excelApp.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet excelSheet = (Worksheet) excelFile.Worksheets [1]; //'1' is Worksheet 1 in the file

excelSheel.Cells [1, 1] = "Some String"; 
//Note that this starts at index (1,1), unlike most arrays that would start at (0,0)

//Also if you to say specify a value based off for instance an array's length you need something like this
//excelSheet.Cells [(object) (arrayHeight + 1), 1] = "";
//note the use of "(object)"

excelFile.Close(true, Type.Missing, Type.Missing);

(warning, if your program crashes, or stops abruptly, you need to go to task manager then processes and close the EXCEL.exe, I was never able to figure out how to get around that)

Edited by JOSheaIV
Member Avatar for Jboctor8

Thank you for your response ddanbe
Im not sure i understand.How Do I reference each individual text box to cell, and what code do i put in the event handler for the button click

Member Avatar for ddanbe

You should in essence follow the way to act proposed by AngelofD4rkness. I did the same :)
If you use Intellisence, you will notice that a worksheet type has a Cells property and a get_Range method. So if you want to use Range do something like this:

using Microsoft.Office.Interop.Excel;

Range rng = null;

// in your Click event handler
 rng = worksheet.get_Range("A1", Type.Missing);
 rng.Value2 = "Mystring";

@AngelofD4rkness (and of course the OP)
If you change excel, in this case with a C# program, you have to take into account that Excel pulls up the "Do you want to save your changes?" dialog if you close it from C#. So the C# program first has to save the changes made in Excel, before closing Excel. I have the code somewhere, if I can find it I will post it. But seeiing your code, I think you can manage to give it a try yourself!

Member Avatar for JOSheaIV

oh please provide us with that I'd love to see it.

However if I remember right, I thought that little bit by default would instinctly save on its own ... could be wrong though, it's been awhile since I used it

Also the error I talked about, the more I think about it, that MIGHT have been something else doing it on my end ... might have to look into that (unless this is the save think you were talking about, or related to it I should say)

Member Avatar for ddanbe

@ AngelofD4rkness, have a look here. I think you may find what you need. Success!

Member Avatar for JOSheaIV

Cool thanks, I'll make sure to add this to my C# collection (I have a collection of websites that teach certain features of C# well).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.