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


  • 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
            '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
            'But this doesn't work... I get an error. Please Help!
        End Sub

    Friday, March 02, 2012 5:36 PM


  • 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

    Monday, March 05, 2012 6:59 AM