Pls. Help!!! HOW DO I IMPORT AND EXPORT

[omotoyosi]

Hello All,
I'm Here Again!!! How Do I Import From Excel Or Csv To Vb.net Database.
Moreso, I Want A Code To Export From My Database To Excel Or Csv.

Great Programmers
Pls, Help Me Out It Is Urgent!!!!

[Mr.Wobbles]

In order to interface with excel you have to make the reference to it, go to Project -> Add Reference -> under the COM tab select Microsoft Excel 11.0 Object Library (could be a different number based on your version).

After that you can use functions that are reserved for Excel this next part can get pretty tedious. It is probably best to create a loop that keeps calling information from your DB dynamically. This is an example of some code I used to export:

Sub exportToExcel()

        'Declare Variables to create the excel application, workbook, sheet and chart
        Dim xlApp As Excel.Application
        Dim xlWkb As Excel.Workbook

        'Create the excel application
        xlApp = CreateObject("Excel.Application")

        'Add the workbook to the excel application
        xlWkb = xlApp.Workbooks.Add

        'Set up connection for database
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter

        Dim sql As String

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =  CONNECTION STRING"

        'Open the Connection to the database
        con.Open()

        'Pull information from the database
        sql = "SELECT * FROM table_name"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Survey Results")

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        Dim numRows As Integer = ds.Tables("Survey Results").Rows.Count - 1

        xlSessionCommentsExport(numRows, ds, xlWkb)
        xlBestExport(numRows, ds, xlWkb)
        xlTopicsExport(numRows, ds, xlWkb)
        xlNumericalExport(numRows, ds, xlWkb)
        xlSummaryExport(numRows, ds, xlWkb, con)
        With xlWkb
            .Sheets("Sheet1").Select()
            .ActiveSheet.Delete()
            .Sheets("Sheet2").Select()
            .ActiveSheet.Delete()
            .Sheets("Sheet3").Select()
            .ActiveSheet.Delete()
        End With

        xlApp.Visible = True
        con.Close()
    End Sub

Because it can get pretty long I used functions to actually insert the data, the xlSessionCommentsExport is probably the shortest so i will give that to you

Private Sub xlSessionCommentsExport(ByVal numRows, ByVal ds, ByVal xlWkb)

        Dim i As Integer
        Dim dsRow As DataRow

        'Add the worksheet to the workbook
        Dim xlSessionComments As Excel.Worksheet
        xlSessionComments = xlWkb.Worksheets.Add
        xlSessionComments.Name = "Session Comments"

        With xlSessionComments
            .Range("A1:B1").Merge()
            .Range("A1:B1").FormulaR1C1 = "Session Comments"

            For i = 0 To numRows
                'get the current row
                dsRow = ds.Tables("Survey Results").Rows(i)

                'get the values required for this worksheet out of the current 
                'row and assign them to variables
                sessionComments = CheckDBNullStr(dsRow.Item("sessionComments"))
                .Range("B" & i + 2).FormulaR1C1 = sessionComments
            Next
            .Columns("B").WrapText = True
            .Columns("B:B").ColumnWidth = 110
            .Rows("1:" & numRows + 2).autofit()
        End With

    End Sub

In order to get what would actually be used to put the information in Excel go into Excel and create a Macro that does what you want to do and then there is a way to look at the VB code that does that macro. You will have to change a little bit, but the majority should work just fine.

Other than that I have never imported anything, but it should be the same except backwards. You will have to open the actual file from the program and use these functions to read it in - google a little bit that should be pretty easy. If I wasn't clear on anything don't hesitate to ask.