Imports System.Data.OleDb
Imports System.IO
Imports System.Data
Imports Microsoft.Win32
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class frmMain

    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click

        'Initialize the objects before use
        Dim dataAdapter As New OleDbDataAdapter
        Dim dataSet As New DataSet
        Dim command As New OleDbCommand
        Dim dtMain As New System.Data.DataTable()
        Dim conn As New OleDb.OleDbConnection

        'Assign connection string to connection object
        conn.ConnectionString = sConnString()
        command.Connection = conn
        command.CommandType = CommandType.Text

        'Run query
        command.CommandText = "SELECT Format(ChqNum, '000000') As ChqNum, Format(ChqDate, 'mm/dd/yyyy') As ChqDate, Format(Amount, 'Standard') As Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit, Format(Voided, 'mm/dd/yyyy') As Voided FROM tblJBRHist"
        dataAdapter.SelectCommand = command

        Try

            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

            Dim oExcel As Excel.Application
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add(Type.Missing)
            oSheet = oBook.Worksheets(1)

            Dim dc As System.Data.DataColumn
            Dim dr As System.Data.DataRow
            Dim colIndex As Integer = 0
            Dim rowIndex As Integer = 0

            'Fill data to datatable
            conn.Open()
            dataAdapter.Fill(dtMain)
            conn.Close()

            'Export the Columns to CSV file
            For Each dc In dtMain.Columns
                colIndex = colIndex + 1
                oSheet.Cells(1, colIndex) = dc.ColumnName
            Next

            'Export the rows to CSV file
            For Each dr In dtMain.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each dc In dtMain.Columns
                    colIndex = colIndex + 1
                    oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                Next
            Next

            'Set final path
            Dim fileName As String = "\ExpJBRHistory" + ".csv"
            Dim finalPath = sExportString() + fileName

            'Save file in final path
            oBook.SaveAs(finalPath, XlFileFormat.xlCSV, Type.Missing, _
            Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

            'Release the objects
            ReleaseObject(oSheet)
            oBook.Close(False, Type.Missing, Type.Missing)
            ReleaseObject(oBook)
            oExcel.Quit()
            ReleaseObject(oExcel)
            
            MessageBox.Show("Export done successfully!")

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)

        End Try

    End Sub

    Private Sub ReleaseObject(ByVal o As Object)

        Try

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)

            End While

        Catch

        Finally

            o = Nothing

        End Try
   
   End Sub
   
End Class

Select allOpen in new window