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
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".
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
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
pls try
Open in new window
Regards