Friday, March 02, 2012 5:36 PM
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
Monday, March 05, 2012 6:59 AMModerator
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.
MSDN Community Support | Feedback to us
- Marked As Answer by Shanks ZenMicrosoft Contingent Staff, Moderator Monday, March 12, 2012 9:24 AM