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
KevinInformation TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinInformation TechnologyAuthor 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
KevinInformation TechnologyAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.