Solved

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

Posted on 2016-08-03
5
47 Views
Last Modified: 2016-08-03
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

0
Comment
Question by:printmedia
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,
pls try

mm\/dd\/yyyy

Open in new window

Regards
0
 

Author Comment

by:printmedia
Comment Utility
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".
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
one way to solve it is to change your sql to give back numbers as dates you can then format them to dates or to change the sql to give back the formatted text of the date
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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

0
 

Author Comment

by:printmedia
Comment Utility
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now