Merge multiple Excel sheets using VB.net

[shazeem]

I have a few excel files and i want to merge all the files into one. All the data can be merged into one sheet in the final excel file. I have searched a lot but coudn't find a working answer.

[pritaeas]

http://www.dotnetperls.com/excel-vbnet
https://support.microsoft.com/en-us/kb/301982

[shazeem]

This is the code which i have now. The application now reads from a form and saves the data into an excel file and its copies the file into the backup folder. i need the code for reading the files from the backup folder and merge into one single excel sheet.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    If xlApp Is Nothing Then
        MessageBox.Show("Excel is not properly installed!!")
        Return
    End If
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)
    'xlWorkSheet.Cells(1, 1) = "Employee ID"
    'xlWorkSheet.Cells(1, 2) = "Employee Name"
    'xlWorkSheet.Cells(1, 3) = "Type of Request"
    'xlWorkSheet.Cells(1, 4) = "From Date"
    'xlWorkSheet.Cells(1, 5) = "End Date"
    Dim values(,) As String = _
    { _
        {txtEID.Text, txtENAME.Text, cmbTYPE.Text, dtFrom.Text, dtEnd.Text, TextBox1.Text} _
    }
    Dim value_range As Excel.Range = xlWorkSheet.Range("A2", "F2")
    value_range.Value2 = values
    Dim fil As String
    Dim d As String
    Dim T As String
    T = Now.TimeOfDay.ToString
    d = DateTime.Now.ToString
    d = Regex.Replace(d, "[^\w\\-]/", " ")
    d = d.Replace(":", " ")
    d = d.Replace("/", " ")
    d = d.Replace("-", " ")
    T = T.Replace(":", " ")
    T = T.Replace(".", " ")
    fil = "C:\Excel Folder\" + txtENAME.Text + "" + txtEID.Text + "" + d + "" + T + ".xls"
    xlWorkBook.SaveAs(fil, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    '"C:\Excel Folder\csharp-Excel.xls"
    xlWorkBook.Close(True, misValue, misValue)
    xlApp.Quit()
    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlApp)
    MessageBox.Show("Excel file created , you can find the file C:\Excel Folder\csharp-Excel.xls")
    For Each foundFile As String In My.Computer.FileSystem.GetFiles(mainfold, Microsoft.VisualBasic.FileIO.SearchOption.SearchAllSubDirectories, "*.*")
        Dim foundFileInfo As New System.IO.FileInfo(foundFile)
        My.Computer.FileSystem.CopyFile(foundFile, "C:\Excel Folder\Backup\" & foundFileInfo.Name, False)
    Next
End Sub