Imports Microsoft.Office.Interop
Module ExcelModule
    Public Sub ExportToExcel(ByVal FileName As String, ByVal dt As DataTable)
        Dim oExcel As Excel.ApplicationClass = Nothing
        Dim oBook As Excel.WorkbookClass = Nothing
        Dim oBooks As Excel.Workbooks = Nothing
        Dim oXLWsheet As Excel.Worksheet = Nothing
        Dim oRange As Excel.Range = Nothing
        Dim intColumn, intRow, intColumnValue As Integer


        Try
            'CREATE A NEW EXCEL OBJECT
            oExcel = New Excel.Application
            'HIDE THE EXCEL APPLICATION
            oExcel.Visible = False
            'CREATE A NEW BOOKS OBJECT
            oBooks = oExcel.Workbooks
            'CREATE A NEW BOOK OBJECT
            oBook = oBooks.Add
            'CREATE A NEW WORKSHEET OBJECT
            oXLWsheet = oBook.Sheets(1)
            'CREATE A NEW CELLS OBJECT (RANGE)
            oRange = oXLWsheet.Cells

            'SET THE COLUMN NAMES
            For intColumn = 0 To dt.Columns.Count - 1
                oRange(1, intColumn + 1) = dt.Columns(intColumn).ColumnName.ToString
            Next
            'SET THE CELLS VALUES
            For intRow = 0 To dt.Rows.Count - 1
                For intColumnValue = 0 To dt.Columns.Count - 1
                    oRange(intRow + 2, intColumnValue + 1) = dt.Rows(intRow).ItemArray(intColumnValue).ToString
                Next
            Next
            'SAVE THE WORKSHEET (PROMPT FOR OVERWRITE)
            oXLWsheet.SaveAs(FileName)

        Catch ex As Exception
            MessageBox.Show("Unable to export excel file" & Environment.NewLine & ex.Message, "Unable to export file")
        Finally
            'RELEASE THE CELLS OBJECT
            If Not oRange Is Nothing Then
                Release(oRange)
            End If
            'RELEASE THE WORKSHEET OBJECT
            If Not oXLWsheet Is Nothing Then
                Release(oXLWsheet)
            End If
            'CLOSE AND RELEASE THE BOOK OBJECT
            If Not oBook Is Nothing Then
                oBook.Close(False)
                Release(oBook)
            End If
            'RELEASE THE BOOKS OBJECT
            If Not oBooks Is Nothing Then
                Release(oBooks)
            End If
            'QUIT AND RELEASE THE EXCEL OBJECT
            If Not oExcel Is Nothing Then
                oExcel.Quit()
                Release(oExcel)
            End If
        End Try
    End Sub

    Private Sub Release(ByVal o As Object)
        Try
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(o) <> 0
                'CONTINUE UNTIL ALL OBJECTS ARE RELEASED
            End While
        Catch
        Finally
            'SET THE OBJECT = NOTHING
            o = Nothing
        End Try
    End Sub
End Module