How to bring over column formatting

Good Morning,

I am coding in VB.Net and am exporting data from an Access 2013 database in to Excel 2013.

While my code (below) does export the data correctly, I’ve noticed that the export does not bring over the column formatting, such as decimal placing etc.

Kindly advise how I can bring over the formatting as well?

Imports System.Data.OleDb
Imports System.IO


Public Class frmExport

    Private Sub btnExportCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportCancel.Click

        Me.Close()

    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim conn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

        Try

            'get connection string declared in the modFunctions.vb and assing it to conn variable
            conn = New OleDbConnection(sConnString)
            conn.Open()

            If rdoExportSBT.Checked = True And rdoExportAll.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\test\All_SBT.xls].[SBT_EXPORT] FROM tblSBTHist"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

            ElseIf rdoExportSBT.Checked = True And rdoExportRange.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\test\DateRange_SBT.xls].[SBT_EXPORT] FROM tblSBTHist WHERE ChqDate BETWEEN #" & dtpExportFrom.Value.ToString("yyyy/MM/dd") & "# and #" & dtpExportTo.Value.ToString("yyyy/MM/dd") & "#"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

            ElseIf rdoExportBSBIL.Checked = True And rdoExportAll.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\test\All_BSBIL.xls].[BSBIL_EXPORT] FROM tblBSBILHist"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

            ElseIf rdoExportBSBIL.Checked = True And rdoExportRange.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\test\DateRange_BSBIL.xls].[BSBIL_EXPORT] FROM tblBSBILHist WHERE ChqDate BETWEEN #" & dtpExportFrom.Value.ToString("yyyy/MM/dd") & "# and #" & dtpExportTo.Value.ToString("yyyy/MM/dd") & "#"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

            Else

                MsgBox("Please select a unit to export.")

            End If

        Catch ex As Exception

        Finally

            conn.Close()

        End Try

    End Sub

End Class

Select allOpen in new window

Regards,
K