Avatar of printmedia
printmedia
 asked on

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

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

Open in new window

Visual Basic.NET

Avatar of undefined
Last Comment
printmedia

8/22/2022 - Mon
Rgonzo1971

Hi,
pls try

mm\/dd\/yyyy

Open in new window

Regards
printmedia

ASKER
Thank you for your reply.

I tried mm\/dd\/yyyy

Did not work.

But I did notice that if I go into each cell and double click the cell, it changes it to mm/dd/yyyy (I saw this before applying your code suggestion). And when I go to the column "Format" it says "Custom".
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rgonzo1971

Maybe you could change the type of the data  with something like this
for each c as oXLApp.Range in oXLApp.Range(oXLSheet.Range("E2"), oXLSheet.Range("E1048576").End(oXlApp.XlDirection.xlUp))
    c.Value = CInt(DateValue(c.Value))
Next

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
printmedia

ASKER
I changed the sql to give back the formatted text of the date as you suggested and it correctly exported the date as MM/DD/YYY to Excel. I didn't need to use mm\/dd\/yyyy

Here's what I did in the sql statement:

CONVERT(VARCHAR,BlanketOrderDate,101) AS BlanketOrderDate

Open in new window


Thanks for your help.