Exporting data to Excel from VB6/VBA

[Darren Gulliver]

Hi,

I'm trying to export data to excel but the dates are not formatting correctly.
Once the data is exported I then loop through the columns changing their
type based on the column header ie:

For i = 1 To clv.ColumnHdrsCLVC.Count
' Format spreadsheet to appear like the grid - DG
Select Case oWsh.Cells(1, i).Value
Case "T/D Balance", "V/D Balance", "Debit Amount", "Credit
Amount", _
"Balance", "Debit", "Credit", "Amount", "Trade Date
Balance", _
"Countervalue"

oWsh.Columns(i).NumberFormat = "###,###,#00.00"

Case "Base Rate Value", "Sales Credit Rate", "Haircut", "Fee
Rate", "Base Rate Indicator", "Repo Rate", "Accrued Interest To Date", _
"Base Rate Spread", "Implied Rate"

oWsh.Columns(i).NumberFormat = "#,##0.00"

Case "Quantity"
oWsh.Columns(i).NumberFormat = "###,###,#00"

Case Else

oWsh.Columns(i).NumberFormat = "@"

End Select
Next i

Because I have not specified a date Column for example "Value Date" the
formatting will be Text. When looking at the data in excel the value in
"Value Date" is displayed as 39148 and not 03/07/2007. If I insert another
Case before Case Else:

Case "Value Date"
oWsh.Columns(i).NumberFormat = "dd-mm-yyyy"
The value is then formatted american style, but this only happens if dates
are 01 to 12. After dates 13 it formats correctly. ie 01/07/2007 to
12/07/2007 will format 07/01/2007 to 07/12/2007 and 13/07/2007 etc is ok.

Any ideas?
Thanks in advance.
Darren

 

[Satish Sadasivam]

Hi,

I'm trying to export data to excel but the dates are not formatting correctly.
Once the data is exported I then loop through the columns changing their
type based on the column header ie:

For i = 1 To clv.ColumnHdrsCLVC.Count
' Format spreadsheet to appear like the grid - DG
Select Case oWsh.Cells(1, i).Value
Case "T/D Balance", "V/D Balance", "Debit Amount", "Credit
Amount", _
"Balance", "Debit", "Credit", "Amount", "Trade Date
Balance", _
"Countervalue"

oWsh.Columns(i).NumberFormat = "###,###,#00.00"

Case "Base Rate Value", "Sales Credit Rate", "Haircut", "Fee
Rate", "Base Rate Indicator", "Repo Rate", "Accrued Interest To Date", _
"Base Rate Spread", "Implied Rate"

oWsh.Columns(i).NumberFormat = "#,##0.00"

Case "Quantity"
oWsh.Columns(i).NumberFormat = "###,###,#00"

Case Else

oWsh.Columns(i).NumberFormat = "@"

End Select
Next i

Because I have not specified a date Column for example "Value Date" the
formatting will be Text. When looking at the data in excel the value in
"Value Date" is displayed as 39148 and not 03/07/2007. If I insert another
Case before Case Else:

Case "Value Date"
oWsh.Columns(i).NumberFormat = "dd-mm-yyyy"
The value is then formatted american style, but this only happens if dates
are 01 to 12. After dates 13 it formats correctly. ie 01/07/2007 to
12/07/2007 will format 07/01/2007 to 07/12/2007 and 13/07/2007 etc is ok.

Any ideas?
Thanks in advance.
Darren

Hi Darren,

I am also facing the same issue. This is something to do with Locale
of the machine and also the format of the date columns.
Pls let me know, if you have solved that issue.

Thanks,
Satish