Merging Multiple Excel Files to a Single Dataset / Excel File

'The Merging Function
    Public Sub MergeExcelInDataSet(ByVal strExcelFile As String, ByRef ds As DataSet)
        Dim objExcel As Microsoft.Office.Interop.Excel.Application
        Dim objWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim tmpWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open(strExcelFile)

        Dim strSheetName(objWorkBook.Worksheets.Count - 1) As String
        Dim i As Integer = 0
        For Each tmpWorkSheet In objWorkBook.Worksheets
            strSheetName(i) = tmpWorkSheet.Name
            i += 1
        Next

        objExcel.Quit()
        releaseObject(objExcel)
        releaseObject(objWorkBook)
        releaseObject(tmpWorkSheet)


        Dim conn As New System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
        For Each str As String In strSheetName
            Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [" & str & "$]", conn)
            da.Fill(ds, str)
        Next

    End Sub


'The Exporting Function
    Public Sub ExportDatasetToExcel(ByVal ds As DataSet, ByVal strExcelFile As String)

        Dim conn As New System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
        conn.Open()

        Dim strTableQ(ds.Tables.Count) As String

        Dim i As Integer = 0

        'making table query
        For i = 0 To ds.Tables.Count - 1

            strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

            Dim j As Integer = 0
            For j = 0 To ds.Tables(i).Columns.Count - 1
                Dim dCol As DataColumn
                dCol = ds.Tables(i).Columns(j)
                strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
            Next
            strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
            strTableQ(i) &= ")"

            Dim cmd As New OleDbCommand(strTableQ(i), conn)
            cmd.ExecuteNonQuery()

        Next

        'making insert query
        Dim strInsertQ(ds.Tables.Count - 1) As String
        For i = 0 To ds.Tables.Count - 1
            strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
            Next
            strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
            strInsertQ(i) &= ")"
        Next

        'Now inserting data
        For i = 0 To ds.Tables.Count - 1
            For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
                Dim cmd As New OleDbCommand(strInsertQ(i), conn)
                For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                    cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
                Next
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()

            Next
        Next
    End Sub

    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


Example:
        Dim dsFinal As New DataSet
        MergeExcelInDataSet("d:\Book1.xls", dsFinal)
        MergeExcelInDataSet("d:\Book2.xls", dsFinal)
        MergeExcelInDataSet("d:\Book3.xls", dsFinal)
        MergeExcelInDataSet("d:\Book4.xls", dsFinal)
        MergeExcelInDataSet("d:\Book5.xls", dsFinal)
        'all the excels are in dsFinal dataset now
        'we will export this dataset to excel
        ExportDatasetToExcel(dsFinal, "d:\my.xls")