Problem Passing data from VB.Net Text box and appending an Excel Cell with that data

Question

In this example I am just tring to pass and manipulate data between VB.Net and Excel. Eventually I want to append the excel file on a new row everytime the user fills out the data on my form and clicks submit but for right now I am just trying to pass a string in a text box to the A1 cell in my excel file.
Here's what I have so far:

 Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
       

        Dim filename As String
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
        xlApp.Visible = True


        filename = "c:\manipulateExcel.xlsx"


            xlBook = CType(xlApp.Workbooks.Open(filename), Microsoft.Office.Interop.Excel.Workbook)
            xlSheet = CType(xlBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

        'If I do this:
        txtCusname.Text = xlSheet.Cells(1, 1).Text
        xlBook.Save()
        xlBook.Close()
        'the string contained by txtcusname.Text changes to the value of my excel spreadsheet A1 Cell
        'What I want to do is the inverse


        'What I need to do is this
        'So that I can change and eventually append the Excel file with the contents of txtCusname.Text
        xlSheet.Cells(1, 1).text = txtCusname.Text
        xlBook.Save()
        xlBook.Close()
        'But this doesn't work... I get an error. Please Help!



    End Sub

Answer 1

Hi Michael,

It’s nice to see you here.

When you set the value to excel cell using the codes in your post, the exception Unable to set the Text property of the Range class will be thrown. The code will work:

xlSheet.Cells(1, 1) = txtCusname.Text

Or you could use:

xlSheet.Cells(1, 1).value = txtCusname.Text

Please check this link about Range Class for detailed information.

I hope this will be helpful.

Best regards,

---

Shanks Zen
MSDN Community Support | Feedback to us