Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to Excel from VB6/VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to Excel from VB6/VBA
On Aug 6, 11:46 am, Darren Gulliver <Darren
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting data to Excel | Excel Worksheet Functions | |||
Exporting data to Excel | Excel Discussion (Misc queries) | |||
Exporting data to EXCEL | Excel Programming | |||
Exporting data to Excel | Excel Programming | |||
Exporting Data from Excel | Excel Programming |