RE: TextToColumns issue (Posted by Cetin Basoz) - www.foxite.com

> Hi freinds,
>
> I'm working with excel automation where I want to pull excel data to my table . But sometime it is failed becoz field type in excel is differ. My code will only work when sepecific data range in excel file is general format i/o text format. So I decided to go for excel inbuilt TextToCoumns().
>
> code is
>

> #Define xlDelimited 1
> #Define xlDoubleQuote 1
> .Selection.TextToColumns("A1", DataType=xlDelimited, ;
>   TextQualifier=xlDoubleQuote, ConsecutiveDelimiter=.F., Tab=.T., ;
>   Semicolon=.F., Comma=.F., Space=.F., Other=.F.,FieldInfo=Array(1,1),;
>   TrailingMinusNumbers=.T.)
>

> But VFP treat datatype,textqualifier as variable and throw error msg, so I change the below way
>
>

> .Selection.TextToColumns("A1", 1, ;
> 1, .F., .T., ;
> .F., .F., .F., .F.,[1,1],.T.)
>

> But again I'm getting OLE error msg !
>
> How can I define FieldInfo=Array(1,1) in VFP OLE ?
>
> Subhankar

#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

#Define xlTextQualifierDoubleQuote	1

lcFileName = Sys(5)+Curdir()+'myemployee.txt'
Use employee
Copy Fields ;
	first_name,last_name,hire_date,emp_id,birth_date ;
	to myEmployee.txt Type Delimited

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(m.lcFileName,,,xlDelimited,,,,,.t.,,,, @arrFldInfo)
  .visible = .t.
endwith