Excel 2007/2010 dates into VFP9

[mbh27]

In a VFP9 app I extract info from a DBF and use automation to format and write to an XLS sheet. In Excel, the user can then update certain cells before saving in 95/5.0 format. VFP then reads the resulting XLS file into a DBF and updates other parts of the system from this.

This was all fine using Excel in versions up to 2003, but with 2007 and 2010 if any date cells are edited, they become blank when reconverting to DBF format. (Existing dates that are exported but not edited in Excel remain intact upon reconversion.)

When creating the initial file with Automation the NumberFormat of the date columns is set to "Dd/Mm/Yy". The file is read back by VFP using the (rather outdated) 'APPEND FROM <fname> TYPE XLS'.

Clearly I now need a more sophisticated approach which must be able to cope with both the 'old' and 'new' Excel versions. What advice could anyone offer (preferably requiring the least changes to the current code)?

 

[Olaf Doschke]

Excel5 does not seem to know a real date type, but store a reference value to 12/30/1899, vfp cannot input that as a date, but as an integer.

So in your input define integer fields instead of date fields and then add that integers to Date(1899,12,30) to get the date.

Bye, Olaf.

 

[cbasoz]

http://www.foxite.com/archives/0000153776.htm

Cetin Basoz
MS Foxpro MVP, MCP

 

[TamarGranor]

TYPE XLS uses Excel 2.0 format. For Excel 5.0, which handles dates better, use TYPE XL5.

Tamar

 

[Olaf Doschke]

APPEND FROM XLS does correctly import an Excel 5 XLS file too, I don't see that the APPEND fails because of using the wrong type, foxpro rather seems to read the excel version from the xls file than relying on the type given in the command option.

In fact there is APPEND FROM ... TYPE XLS, XL5 and XL8, also IMPORT FROM ... TYPE XLS, XL5 and XLS.

As I played with APPEND I tried with XL5 and it still does not import dates as dates, but as integers, becuase that's what excel uses internally as dates and stores into Excel 5 files. If the cursor you append to does have an integer column it imports integers as difference to 12/30/1899.

Also using IMPORT FROM ... TYPE XL5 a cursor with an integer field is generated.

The whole design of exporting data to XLS to edit and then import it is unelegant, but if that is accepted that, the only way to APPEND or IMPORT excel data is either as XL5 or via ADODB.

Bye, Olaf.