I have a date problem (please don’t tell me I should be on the ‘Lonely Hearts’ forum!)
My main application form has SET DATE DMY in the INIT (I’m in the UK) and no other SET DATE called elsewhere. All the actions relevant to this query come under the management of this one form. In one of the tables I have a field [sptRenewal] set to DATE format.
One of the tasks this application is required to do is to export to a formatted Excel file. For this I am selecting relevant fields into a cursor (vtSAMLst) and then using Excel Automation to create the Excel sheet.
Exporting the field sptRenewal, if I use (after defining oSheet) ...
oSheet.Cells(RowNumber,ColumnNumber).Value = vtSAMLst.sptRenewal
this error occurs (and it occurs at the first iteration):
OLE error code 0x800a03ec: Unknown COM status code.
I have overcome that by adding a new field into the SELECT statement for the cursor with ...
SELECT sptRenewal, SPACE(8) as spt_Renewal, otherfield, otherfield etc
and then, after the SELECT is completed, issuing ...
REPLACE spt_Renewal with DTOC(sptRenewal) IN vtSAMLst ALL
I follow this with the export coding including ...
oSheet.Cells(RowNumber,ColumnNumber).Value = vtSAMLst.spt_Renewal
So far so good and everything works with expected results - no error message and the Excel sheet appears, nicely laid out etc. However, on close inspection I have noticed that not all dates are being carried over correctly! Most are fine: 19/07/17, 21/04/17, 31/08/18 etc (remember, I’m in the UK).
But, for instance, 06/07/17 has become 07/06/17 and 10/08/17 has become 08/10/17. These dates have all been transferred by the same code line and into the same Excel column, by the same routine in the same session. They are not together at the start or end of the list. In the cursor [vtSAMLst] both sptRenewal and spt_Renewal are correct and correspond with each other.
The original table is a free table so there is no field formatting and the form textbox for the date has no Format property or Input Mask set.
At an early stage I had inlcuded the following in the transfer coding to try to eradicate the first error message but now that I am using character date this is irrelevant and has been removed ...
oRange.EntireColumn.NumberFormat = "dd/mm/yy"
If anyone can tell me what I am missing I would be very grateful.
My main application form has SET DATE DMY in the INIT (I’m in the UK) and no other SET DATE called elsewhere. All the actions relevant to this query come under the management of this one form. In one of the tables I have a field [sptRenewal] set to DATE format.
One of the tasks this application is required to do is to export to a formatted Excel file. For this I am selecting relevant fields into a cursor (vtSAMLst) and then using Excel Automation to create the Excel sheet.
Exporting the field sptRenewal, if I use (after defining oSheet) ...
oSheet.Cells(RowNumber,ColumnNumber).Value = vtSAMLst.sptRenewal
this error occurs (and it occurs at the first iteration):
OLE error code 0x800a03ec: Unknown COM status code.
I have overcome that by adding a new field into the SELECT statement for the cursor with ...
SELECT sptRenewal, SPACE(8) as spt_Renewal, otherfield, otherfield etc
and then, after the SELECT is completed, issuing ...
REPLACE spt_Renewal with DTOC(sptRenewal) IN vtSAMLst ALL
I follow this with the export coding including ...
oSheet.Cells(RowNumber,ColumnNumber).Value = vtSAMLst.spt_Renewal
So far so good and everything works with expected results - no error message and the Excel sheet appears, nicely laid out etc. However, on close inspection I have noticed that not all dates are being carried over correctly! Most are fine: 19/07/17, 21/04/17, 31/08/18 etc (remember, I’m in the UK).
But, for instance, 06/07/17 has become 07/06/17 and 10/08/17 has become 08/10/17. These dates have all been transferred by the same code line and into the same Excel column, by the same routine in the same session. They are not together at the start or end of the list. In the cursor [vtSAMLst] both sptRenewal and spt_Renewal are correct and correspond with each other.
The original table is a free table so there is no field formatting and the form textbox for the date has no Format property or Input Mask set.
At an early stage I had inlcuded the following in the transfer coding to try to eradicate the first error message but now that I am using character date this is irrelevant and has been removed ...
oRange.EntireColumn.NumberFormat = "dd/mm/yy"
If anyone can tell me what I am missing I would be very grateful.