CODEWITHSUNDEEP
vb.netexceltextboxsave
Rushi
Rushi

Reputation: 5

saving Textbox data to excel in vb.net

I need help with saving user entered data (textbox) to an excel file. The excel file should be created on desktop automatically. I can add one textbox to excel but not multiple. If possible can you give code on being able to open the same file when btnopen is clicked, thank you!

my code right now for save button.

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    SaveFileDialog.Filter = "CSV Files (*.csv*)|*.csv"
    If SaveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK _
    Then
        My.Computer.FileSystem.WriteAllText _
        (SaveFileDialog.FileName, txtBMI.Text, True)
    End If


    table.Rows.Add(DateTimePicker1.Text, txtBMI.Text, txtHeight.Text, txtWeight.Text)
    DataGridView1.DataSource = table

End Sub
  1. https://i.sstatic.net/H73M8.jpg
  2. https://i.sstatic.net/Rjtjw.jpg

Upvotes: 0

Views: 2770

Answers (2)

black.empiricist
black.empiricist

Reputation: 47

Add first the Microsoft Excel 12.0 Object Library via

Project>Properties>References>Add>COM>Type Libraries>Microsoft Excel 12.0 Object Library

Add releaseObject function (To release the excel file in the app):

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

After adding, declare the necessary variables so that it's easier to control the excel file:

    Dim appXL As Excel.Application
    Dim wbXl As Excel.Workbook
    Dim shXL As Excel.Worksheet
    Dim raXL As Excel.Range
    ' Start Excel and get Application object.
    appXL = CreateObject("Excel.Application")
    ' Add a new workbook.
    wbXl = appXL.Workbooks.Add
    shXL = wbXl.ActiveSheet
    shXL.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
    shXL.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3
    appXL.Visible = False

Add the headers with bold text:

    With shXL.Range("A1", "D1")
                .Font.Bold = True
    End With

    shXL.Cells(1,1) = "Date"
    shXL.Cells(1,2) = "Height"
    shXL.Cells(1,3) = "Weight"
    shXL.Cells(1,4) = "BMI"
    shXL.Cells(2,1) = DateTimePicker1.Text
    shXL.Cells(2,2) = txtHeight.Text
    shXL.Cells(2,3) = txtWeight.Text
    shXL.Cells(2,4) = txtBMI.Text

Next, open the excel file.

    appXL.Visible = True
    appXL.UserControl = True
    shXL.SaveAs("C:\Book1.xml")
    raXL = Nothing
    shXL = Nothing
    wbXl = Nothing
    appXL = Nothing
    releaseObject(shXL)
    releaseObject(wbXl)
    releaseObject(appXL)

Upvotes: 0

The scion
The scion

Reputation: 972

I think you need to use a supported library of creating Excel file and not to do it with file.create as you did. You can use Microsoft Excel 12.0 Object Library and add a refernce to the bin folder or other dll of working with Excel instance.

Dim xlApp As Excel.Application = New
Microsoft.Office.Interop.Excel.Application()

You can see it here - http://vb.net-informations.com/excel-2007/vb.net_excel_2007_create_file.htm

Upvotes: 0

Related Questions