Date Problem exporting to Excel

[SimplyES]

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.

 

[JRB-Bldr]

I can't tell you what you are missing, but my first question would be:
*Are the same Dates and/or the Dates for the same Records appearing in the wrong format each time you run the code on the same data set?

If so I would examine the original data to see if someone has entered a wrong date into the VFP data table.

Good Luck,
JRB-Bldr

 

[TamarGranor]

What happens if you format the cell for a date before you populate it and then you go ahead and put the date value in?

Tamar

 

[atlopes]

I don't work with Excel (automated or otherwise), so this is just guesswork.

No matter what settings your application has for dates, the relevant process seems to be the parsing when you assign the cell value: Excel interprets in the same way it does when we enter the data manually, cell by cell. When there is a day-month ambiguity, it favors the MD format, against the DM you're using. So 19/07/17 will be read as {^2017-07-19} because there is no 19th month, but 06/07/17 as {^2017-06-07} because 6 is acceptable as a month number, and that is the format what Excel favors when parsing the character string (as a date).

In Excel, dates are offset numbers, so instead of inserting a VFP date directly, you may consider entering a number. For instance, [tt]vtSAMLst.sptRenewal - {^1899-12-30}[/tt], as long as you have set the cell format to a Date (as Tamar suggested).

 

[Olaf Doschke]

As you say you export into a formatted excel file, what is the format of that column? It might expect another format than you provide.

Bye, Olaf.

 

[SimplyES]

Thanks to all of you.
atlopes - I suspected something like that was going on but couldn't see why.
Tamar - Spot on. I'm a plonka! The bit I took out because it wasn't having any affect (oRange.EntireColumn.NumberFormat = "dd/mm/yy") I have now put back in. Only now I have put it in the right place. I was using it to change the cell format AFTER I had transferred the data across! Der!

 

[Olaf Doschke]

I was using it to change the cell format AFTER I had transferred the data across!

The funny thing is it actually works this way, but only if the data you put in has no format Excel tries to guess the correct type by default or if the format is set to explictly only mean text "as is". The default setting is "General" and makes Excel try automatic inferences about the data type wanted.

Since the default formatting is that way it is surely better to first set format and then insert data.

I wonder only about one detail in your initial code you put in dates as the native type they are. I would have said Excels OLE module would notice that and wouldn't take the dates as strings but as OLE date type and put them in correctly, though perhaps in MDY format, the default of either Excel or the system. I assume so, as OLE automation always has an intermediate layer marshalling your input to the underlying objects as moderation between the two worlds of VFP and C++ (assuming Office is a C++ product).

It makes me think the original excel template already had a format, which made you slip with any way you put in your data.

Bye, Olaf.

 

[ramanjain]

just convert date type to character type field when you are exporting the date type field.

 

[Mike Lewis]

just convert date type to character type field when you are exporting the date type field.

If you look at the very end of the original post, you will see that he is already doing that. In any case, Excel doesn't know about Visual Foxpro data types. As far as Excel is concerned, every value that is entered into a cell is initially a character. It is the Excel formatting that determines how the value is intepreted.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Excel doesn't know about Visual Foxpro data types. As far as Excel is concerned, every value that is entered into a cell is initially a character

Surely not the native VFP types, but int, char, date, etc are very broadly used types and you can do that:

oExcel = CREATEOBJECT("excel.application")
oExcel.Workbooks.Add
oExcel.ActiveSheet.Cells(1,1)=DATETIME()
oExcel.Visible = .t.

To me, in Germany, this arrives with sys format as German date format. The formatting properties of the cell tell it is detected as user-defined type with format DD.MM.YYYY hh:mm:ss.
I did not do anything else to excel, that is all automatic. You don't convert VFP datetime to text, and the VFP runtime also does not. It's rather like with ODBC, there are transfer data types, something along the line of OLE.datetime, so the datetime is transferred as some binary information without any specific formatting, just like you can select a T-SQL datetime2 into vfp datetime without going through the hoop of converting to the right string representation and back. So data transfer even through OLE servers should be preferred to be done in the most native type possible, let the other end format it as it does and can. If you don't specify any formatting it's likely you get the locale formatting for free without the need to make locale a configuration option of your application.

The automatism of Excel only often fails, when you eg have long serial numbers only consisting of numerical digits, that overflows integer range and then causes scientific notation, where you want the string of digits and not calculate with it. And that is an important topic about dates. Just to achieve Excel displays dates as you want to see them won't help, if Excel treats them as text strings. It won't be able o calculate date differences, for example. What's true is, that a formatting as a date combined with setting cell value to a string of that formatting leads excel to understand and know the date as date value, but the same is true, if you set the formatting and set the cell value to a native VFP date/datetime value, that is marshalled to OLE.date/datetime and excel receives it as Excel.date/datetime. The nature of the value stays and there are only slight range dificulties, eg excel may not be able to go back as far as VFP with historic dates, but the most important range of dates of 1900-2100 should work.

The formatting of the excel cells just determines the display/output, what you input CAN be strings of that same matching formatting, but CAN also be and in my opinion SHOULD be datetimes. If you convert on your own, your conversion has o match the excel formatting, or you get nonsense, that just causes more situations, where you fail. The only reason to convertt to text before outputting to excel is you know you have no such type on the excel side, eg I doubt you can put a Blob inside an excel cell, you can put objects in, but those would also rather be objects you generate by VBA code than just via setting some cell .value

Bye, Olaf.