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

Open in new window


Regards,
K
LVL 1
KevinAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
All you're doing is exporting Data. If you want to format that data in the destination, you'd have to automate Excel, open the Workbook/Worksheet, and then apply your formatting.

You may also be able to format the data in the query before exporting, using various SQL methods like CONVERT, FORMAT. That, whoever, would very likely leave you with the wrong datatypes in your Excel sheet.

You'll probably find it easier to use something like EPLus instead of hassling with the Interops. You can get EPPlus through NuGet, or here: https://github.com/JanKallman/EPPlus
0
 
KevinAuthor Commented:
Thanks. Ill have a look at epplus.

The guide at https://github.com/JanKallman/EPPlus/wiki/Getting-Started is a little tricky as it looks like its in C# (which I am not familiar with). If I can't get it then I will attempt the SQL methods.
0
 
KevinAuthor Commented:
Hi, the epplus was a little too complicated for my programming level. But will revisit it at a later time as it does look very very useful.

So for now, I used the SQL methods, which works well on the two columns I needed adjusting.

Format(ChqNum, '000000') As ChqNum
Format(Amount, 'Standard') As Amount

Open in new window


Appreciate the help.

Kind Regards,
K
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.