Issue in date formats

[Devesh Aggarwal]

I am developing a utility where we need to publish data from an application
hvaing SQL server database to excel. I am doing so. But when i send the data
to excel the date format changes to mm/dd/yyyy even if i am sending it in
dd/mm/yyyy. I have even set the format of that cell as dd/mm/yyyy.

The code i am using to do so is as under:

Dim objDataView As New DataView()
objDataView.Table = objDataSetArray(0).Tables("Condition1")
objDataSetArray(0).Dispose()
If objDataView.Table.Rows.Count > 0 Then
'to get total no. of columns
Dim inttotalcol As Int16 = 0
inttotalcol = objDataView.Table.Columns.Count
'********** for general data insertion in excel
**********
For gintII = 0 To objDataView.Table.Rows.Count - 1
For gintInnerLoop = 0 To
objDataView.Table.Columns.Count - 2
objWB.Application.Range(Chr(gintInnerLoop + 66)
& gintJJ).Select()
objWB.Application.Range(Chr(gintInnerLoop + 66)
& gintJJ).BorderAround()

'From this code i m sending Date.
objWB.Application.ActiveCell.FormulaR1C1 =
objDataView.Table.Rows(gintII).Item(gintInnerLoop).ToString
Next

gintJJ += 1
Next
gintJJ += 1
End If

By this code i m sending data to Excel Sheet. Here i m sending Date in
DD/MM/YYYY fromat. But it is showing in MM/DD/YYYY format in Excel sheet.
Eventhough i did set Format of that perticular column in Excel in
DD/MM/YYYY. I have checked up to last moment date is in DD/MM/YYYY format.

Any suggestions.

Devesh

 

[Norman Jones]

Hi Devesh,

Try:

Format(objDataView.Table.Rows(gintII).Item(gintInnerLoop).ToString, _
"dd,mm,yyyy")