the other day i have some records from a dbf that need to be exported to an excel file. i also need to add some list-based data validation in excel via automation. i am using vfp9 and excel 2002. below is basically what i do.
export to c:\sheet.xls type xl5
loexcel = createobject('excel.application')
loexcel.columns("h").validation.add(xlvalidatelist, xlvalidalertstop, ...)
i had verified the above code works in an interactive session in the command window. but when i open the generated spreadsheet, the data validation is gone!
eventually i found out that vfp exports to excel v5 format, which does not support data validation. however i can add the data validation in the spreadsheet because i am using excel 2002. the save() save in the existing format which is v5 which removes the data validation code without warning.
so i have to do a saveas() to save the generated spreadsheet in excel 2002 format which keeps the data validation code:
it would be better if there is a way for vfp to export dbf to a more update version of excel without requiring an installed copy of excel.