Writing Data to an Existing Excel File

Hello,


I'm fairly new to coding in Visual Basic, and I'm working on a project where I have to allow users to write data to an existing Excel worksheet (by entering the data into a form created in Visual Basic.NET). I've created a form that allows users to enter data such as the following:

Name (First, Last)
Mailing Address
Email Address
Gender
Age

...and so on. I've also created the Excel file and placed it on my C:\ drive, but the problem I'm having is trying to send the data from the VB form to the Excel file itself. Can someone please help me revise my code so that I can get the submitted information from the form to save into the Excel file?
I'm trying to get the information to show up in rows and columns in Excel like the following below (the dashes are just to show that the information should be in rows and columns):

Name--------------------------Mailing Address-------Email Address-------Gender-------Age
John Doe---------------------2300 Jackson Street-------- [email protected]-------Male---------17

When I first created this project, I was told to have users enter data into the form and have the data saved into a text file. I managed to complete that part, but now I need to get the entered data to save into an Excel file instead of a text file. I'm attaching a screen shot to show my form layout. Here is the code that I have so far; I'm having trouble revising it so that it would work with Excel 2007.

HTML Code:

---


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load



    End Sub

    Private Sub txtPhone_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPhone.TextChanged
        lblMessage.Text = "Your phone number is " & txtPhone.Text & "."
    End Sub


    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        lblMessage.Text = "Your name is " & txtName.Text & "." & "Your mailing address is " & txtMailing.Text & ". " & "Your email address is " & txtEmail.Text & ". " & "Your phone number is " & txtPhone.Text & ". " & "Your student ID/Social is " & txtSocialID.Text & ". " & "Your gender is " & txtGender.Text & " ." & "Your race is " & txtRacial.Text & ". " & "Your age is " & txtAge.Text & ". "
        'the above displays the information about the user'


        Dim TargetFile As StreamWriter

        Try
            TargetFile = New StreamWriter("C:\DataInfo.txt", True)
        Catch
            MessageBox.Show("Error opening " & "C:\DataInfo.txt")
        End Try

        Try
            TargetFile.WriteLine(Now())
            TargetFile.Write(txtName.Text & vbTab)

            TargetFile.Write(txtMailing.Text & vbTab)

            TargetFile.Write(txtEmail.Text & vbTab)

            TargetFile.Write(txtEmail2.Text & vbTab)

            TargetFile.Write(txtPhone.Text & vbTab)

            TargetFile.Write(txtSocialID.Text & vbTab)
            TargetFile.Write(txtGender.Text & vbTab)
            TargetFile.Write(txtRacial.Text & vbTab)
            TargetFile.Write(txtGlasses.Text & vbTab)
            TargetFile.Write(txtAge.Text & vbTab)
            TargetFile.Write(txtNotes.Text & vbTab)

            TargetFile.WriteLine()

        Catch
            MessageBox.Show("Error writing file")
        End Try

        TargetFile.Close()




        MessageBox.Show("Text saved to " & "C:\DataInfo.txt")



    End Sub




    Private Sub txtGender_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtGender.TextChanged
        lblMessage.Text = "You have entered " & txtGender.Text & "."
    End Sub

    Private Sub txtGlasses_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtGlasses.TextChanged
        lblMessage.Text = "You have entered " & txtGlasses.Text & "."
    End Sub

    Private Sub txtAge_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAge.TextChanged
        lblMessage.Text = "You have entered " & txtAge.Text & "."
    End Sub

    Private Sub txtReturning_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtReturning.TextChanged
        lblMessage.Text = "You have selected " & txtReturning.Text & "."
    End Sub

    Private Sub txtRacial_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRacial.TextChanged
        lblMessage.Text = "You have entered " & txtRacial.Text & "."
    End Sub

    Private Sub txtName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtName.TextChanged
        lblMessage.Text = "Your name is " & txtName.Text & "."
    End Sub

    Private Sub txtMailing_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMailing.TextChanged
        lblMessage.Text = "Your mailing address is " & txtMailing.Text & "."
    End Sub

    Private Sub txtEmail_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEmail.TextChanged
        lblMessage.Text = "Your email address is " & txtEmail.Text & "."
    End Sub

    Private Sub txtEmail2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEmail2.TextChanged
        lblMessage.Text = "You have confirmed your email address as " & txtEmail2.Text & "."
    End Sub

    Private Sub txtSocialID_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSocialID.TextChanged
        lblMessage.Text = "Your student ID is " & txtSocialID.Text & "."
    End Sub

    Private Sub txtNotes_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNotes.TextChanged
        lblMessage.Text = "Your notes are the following: " & txtNotes.Text & "."
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        txtName.Text = ""
        txtMailing.Text = ""
        txtEmail.Text = ""
        txtEmail2.Text = ""
        txtPhone.Text = ""
        txtSocialID.Text = ""
        txtGender.Text = ""
        txtRacial.Text = ""
        txtGlasses.Text = ""
        txtAge.Text = ""
        txtReturning.Text = ""
        txtNotes.Text = ""
    End Sub
End Class







---

Any help is appreciated. Also, I'm using Visual Studio.NET 2003 and Microsoft Excel 2007.

"Racial Classification"?? how about "Ethnicity"
First, Last? odd...

You'll need to import the Excel Interop and create an Excel object..
I have some code for this.. let me find it

""Racial Classification"?? how about "Ethnicity"
First, Last? odd..."

LOL. Yeah, I just coded what I was told to code. I'm trying to figure out how to get this stuff to show up in Excel instead of a text file. Any input is appreciated. :wave:

Here is a pretty good rundown:

http://vb.net-informations.com/excel...reate_file.htm

Thanks!

Quote:

---

Originally Posted by stateofidleness

Here is a pretty good rundown:

http://vb.net-informations.com/excel...reate_file.htm

---

Lots of good information there but use caution as the person who presented this must have option strict off and or option explicit off as at least one line of code errors out. This is yet another reason I stay away from Office automation.

Incorrect

Code:

---

xlWorkSheet = xlWorkBook.Sheets("sheet1")

---

Better

Code:

---

xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

---

In regards to Ethnicity, gender, wear glasses, planning to return might I suggest changing these to a ComboBox style dropdown list so that there are no misspellings or incorrect information.

Good ideas. Thanks for the suggestions.:)

Try this..

Code:

---

        Dim xlApp As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        Dim xlAppWorkbook As Excel.Workbook = DirectCast(xlApp.Workbooks.Add(), Excel.Workbook)

        'Add textbox value to cell A1
        xlApp.Range("A1").Value = TextBox1.Text
        xlApp.Range("B1").Value = TextBox2.Text
        xlApp.Range("A1").Value = TextBox3.Text
        xlApp.Range("C1").Value = TextBox4.Text
        xlApp.Range("D1").Value = TextBox5.Text
        'and so on...
        xlApp.Visible = True

---

Make sure you include the Microsoft.Office.Interop.Excel reference

And add this:

Code:

---

Imports Microsoft.Office.Interop

---