>Hi All,
>
>I need some help with an Excel Automation process.
>
>I am creating an Excel worksheet from a VFP7 .prg OK and populating it OK from a .txt file, using the QueryTables.Add method. Everything works except being able to change the column format of a table. I then save the worksheet as a dbf4 formatted .dbf.
>
>The syntax formt for the column format taken from the Excel macro is:
> TextFileColumnDataTypes = Array(2,2,2,2,2,2,2) representing a 'Text' format.
>
>I need to change one of those columns to a date format and another to numberic.
>
>VFP does not like the Array() bit.
>
>I have tried setting up an array and entering:
> TextFileColumnDataTypes = @arrayname
>
>and it still doesn't like it.
>
>Any help would be appreciated.
>
>Regards,
>Bill
Could you use OpenText method instead ?
#DEFINE xlDelimited 1
#DEFINE xlFixedWidth 2
#DEFINE xlGeneralFormat 1
#DEFINE xlTextFormat 2
#DEFINE xlMDYFormat 3
#DEFINE xlDMYFormat 4
#DEFINE xlYMDFormat 5
#DEFINE xlMYDFormat 6
#DEFINE xlDYMFormat 7
#DEFINE xlYDMFormat 8
#DEFINE xlSkipColumn 9
#DEFINE xlEMDFormat 10
TEXT TO myVar noshow
0001,VFP7,01/01/2002,skipme,30/01/2003
0002,VFP8,01/31/2003,skipme,29/02/2000
ENDTEXT
lcFileName = fullpath('excelopentexttest.txt')
STRTOFILE(myVar, lcFileName)
dimension arrFldInfo[5,2]
for ix=1 to 5
arrFldInfo[ix,1]=ix
endfor
arrFldInfo[1,2]=xlTextFormat
arrFldInfo[2,2]=xlTextFormat
arrFldInfo[3,2]=xlMDYFormat
arrFldInfo[4,2]=xlSkipColumn
arrFldInfo[5,2]=xlDMYFormat
oExcel = createobject('Excel.Application')
with oExcel
.Workbooks.OpenText(lcFileName,,,xlDelimited,,,,,.t.,,,, @arrFldInfo)
.visible = .t.
endwithCetin
