VB control Excel date format

[brian_choi]

Dear Expert

I want to output the recordset to Excel and set the date format in Excel dd-mm-yyyy. However, when I use below syntax, the outout of format is different under different case

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object



Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

With oSheet
For i = 0 To objRs.Fields.Count - 1
oSheet.Cells(1, i + 1).Value = Trim(CStr(objRs.Fields(i).Name))
objRs.MoveFirst
.Range("F1:F" & objRs.RecordCount + 1).NumberFormat = "dd-mm-yyyy"
.Range("G1:G" & objRs.RecordCount + 1).NumberFormat = "dd-mm-yyyy"
For j = 1 To objRs.RecordCount
If i = 5 Or i = 6 Then
oSheet.Cells(j + 1, i + 1).NumberFormat = "dd-mm-yyyy"
End If
oSheet.Cells(j + 1, i + 1).Value = objRs.Fields(i).Value
objRs.MoveNext
Next
Next
End With


?objRs.Fields(i).Value
23-09-2005
02-09-2005


?oSheet.Cells(j + 1, i + 1).Value
23-09-2005
09-02-2005


Even the date output in recordset is 02-09-2005, and the setting of the ouput of Excel is dd-mm-yyyy. The final output of Excel is still 09-02-2005.
My PC regional setting is in dd-mm-yyyy.

Any suggestion

Thank you very much

Brian choi

[D-niss]

try:

format(.Range("F1:F" & objRs.RecordCount + 1), "dd-mm-yyyy")

or

format(cdate(objRs.Fields(i).Value), "dd-mm-yyyy")

[kkc]

' you should not do numberformating when you insert result to each cells
' you should do number formating after finished insert records, this increase
' performance

' use the following line to do number formatting
' xlWSheet = your worksheet
' Range("A:A"). = the range you want to format, here will format whole col A
' format
' you can define the range yourself

xlWSheet.Range("A:A").NumberFormat = "MM/dd/YYYY"



hope this help.

[brian_choi]

D-niss

When I try
format(.Range("F1:F" & objRs.RecordCount + 1), "dd-mm-yyyy")
Type mismatch error

When I try
format(cdate(objRs.Fields(i).Value), "dd-mm-yyyy")
The output is not in date

Any idea?

[D-niss]

I don't usually set the cell number format before passing the data, I do it at the same time, something like:

With Sheet1
Cells(j + 1, i + 1)= Format(objRs.Fields(i).Value, "mm-dd-yyy")
End With

It sets the correct format and correct value. In essence you wouldn't need this anymore:

.Range("F1:F" & objRs.RecordCount + 1).NumberFormat = "dd-mm-yyyy"
.Range("G1:G" & objRs.RecordCount + 1).NumberFormat = "dd-mm-yyyy"

Also, I would check what is the data type of the date field you're trying to pass, it may shade more light on this.

Best of luck

[brian_choi]

The problem was fixed by using below syntax

With oSheet
For i = 0 To objRs.Fields.Count - 1
.Cells(1, i + 1).Value = objRs.Fields(i).Name
objRs.MoveFirst
For j = 1 To objRs.RecordCount
If i = 5 Or i = 6 Then
.Cells(j + 1, i + 1) = Format(objRs.Fields(i).Value, "dd-mmm-yyyy")
'oSheet.Cells(j + 1, i + 1).Value = objRs.Fields(i).Value
Else
oSheet.Cells(j + 1, i + 1).Value = objRs.Fields(i).Value
End If
objRs.MoveNext
Next
Next
.Range("F1:F" & objRs.RecordCount + 1).NumberFormat = "dd-mm-yyyy"
.Range("G1:G" & objRs.RecordCount + 1).NumberFormat = "dd-mm-yyyy"
End With


Thank you very much D-niss