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.workbooks.open("c:\sheet.xls")
loexcel.columns("h").validation.add(xlvalidatelist, xlvalidalertstop, ...)
loexcel.activeworkbook.save()
loexcel.quit()

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:

loexcel.activeworkbook.saveas('c:\sheet2.xls',xldefault)

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *