troubleshooting Question

Format Excel column date to MM/DD/YYYY in Visual Studio vb.net

Avatar of printmedia
printmedia asked on
Visual Basic.NET
5 Comments1 Solution3766 ViewsLast Modified:
Hi all.

I'm exporting a datagridview control to Excel and I would like to format one of the columns to MM/DD/YYYY once it gets to Excel because the data is exporting to Excel as YYYY-MM-DD

In my code below I tried doing it but it's not changing the date format to MM/DD/YYYY as I would like: oXLSheet.Range("E:E").NumberFormat = "MM/DD/YYYY"

Thank you in advance.

 Dim oXLApp As Excel.Application 'Declare the object variables
        Dim oXLBook As Excel.Workbook
        Dim oXLSheet As Excel.Worksheet

        oXLApp = New Excel.Application  'Create a new instance of Excel
        oXLBook = oXLApp.Workbooks.Add

        oXLSheet = oXLBook.Worksheets(1)
        oXLApp.Visible = True

        Dim sql As String = "" & BuilderFilter() & ""   'The SQL Select statement used to populate the datagridview control when the search button is clicked

        Dim cn As New ADODB.Connection()
        Dim rs As New ADODB.Recordset()
        Dim cnStr As String
        Dim cmd As New ADODB.Command()

        cnStr = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"

        cn.Open(cnStr)
        cmd.ActiveConnection = cn
        cmd.CommandText = sql
        rs = cmd.Execute
        oXLSheet.Range("A1").CopyFromRecordset(rs)
        rs.Close()
        cn.Close()

        'Insert column headers
        oXLSheet.Rows(1).Insert()
        oXLSheet.Cells(1, 1).Value = "Blanket Order No"
        oXLSheet.Cells(1, 2).Value = "Customer Blanket Order No"
        oXLSheet.Cells(1, 3).Value = "Status"
        oXLSheet.Cells(1, 4).Value = "Account"
        oXLSheet.Cells(1, 5).Value = "Blanket Order Date"
        oXLSheet.Cells(1, 6).Value = "Blanket Order End Date"
        oXLSheet.Cells(1, 7).Value = "Blanket Create Date"
        oXLSheet.Cells(1, 8).Value = "Blanket Closed Date"
        oXLSheet.Cells(1, 9).Value = "Item Number"
        oXLSheet.Cells(1, 10).Value = "Quantity"
        oXLSheet.Cells(1, 11).Value = "Minimum Stock Qty"
        oXLSheet.Cells(1, 12).Value = "Item Status"
        oXLSheet.Cells(1, 13).Value = "Item Closed Date"
        oXLSheet.Cells(1, 14).Value = "Notes"

        oXLSheet.UsedRange.Borders.LineStyle = 1
        oXLApp.Columns.AutoFit()

        oXLSheet.Range("A1:N1").Font.Bold = True
        oXLSheet.Range("E:E").NumberFormat = "MM/DD/YYYY"


        oXLBook = Nothing   'Disconnect from Excel (let the user take over)
        oXLApp = Nothing
        oXLSheet = Nothing
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros