Need help from Excel Automation experts!!

[cdms]

I am trying to open a text file in Excel 2000 - (sounds easy!) BUT I need the first two columns of the file to be declared as text (I have leading zero's that I want to keep).

I am recording a macro in excel and then converting the code to VFP. As I'm sure you know, some of the Excel functions have quite a lot of parameters. I am falling down when it comes to the parameters that specify the column types. Below is how the parameter looks in Excel :

FIELDINFO:=ARRAY(ARRAY(1, 2), _
ARRAY(2, 2), ARRAY(3, 1), ARRAY(4, 1), ARRAY(5, 1)

I hope you can help

Thanks

Chris

 

[DEDMOD]

This doesn't seem to match anything in the VBAXL10.chm help-file I have for Excel 2002. Could you post more of what this comes from? Dave Dardinger

 

[cdms]

This code is from Excel 2000 when I am opening the file. It shouldn't be too different from 2002

XLSHEET.OPENTEXT FILENAME:="C:\nickhvfp\xls.auto\SPRZ.TXT", ORIGIN:= _
XLWINDOWS, STARTROW:=1, DATATYPE:=XLDELIMITED, TEXTQUALIFIER:= _
XLDOUBLEQUOTE, CONSECUTIVEDELIMITER:=FALSE, TAB:=FALSE, SEMICOLON:=FALSE _
, COMMA:=TRUE, SPACE:=FALSE, OTHER:=FALSE, FIELDINFO:=ARRAY(ARRAY(1, 2), _
ARRAY(2, 2), ARRAY(3, 1), ARRAY(4, 1), ARRAY(5, 1))

I hope this helps

Thanks

 

[DEDMOD]

Ok, so here's what it says for FIELDINFO under OPENTEXT:

FieldInfo Optional xlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

for XLColumnData Type it says:

XlColumnDataType can be one of these XlColumnDataType constants.

xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date

xlDMYFormat DMY date

xlYMDFormat YMD date

xlMYDFormat MYD date

xlDYMFormat DYM date

xlYDMFormat YDM date

xlEMDFormat EMD date

xlSkipColumn Skip Column

but I don't know whether that's in order, or if it is if it's 1 based or 0 based. There's probably a header file which lists what the values are, or you can experiment and find them. Or maybe you can just write it like: array)... array(3, "text&quot...

Dave Dardinger

 

[cdms]

I think it is the actual word array vfp doesn't like. I keep getting the error : "array.prg does not exist"?????

I am using the numeric qualifier for the above commands in the 2nd parameter but I think you can do it either way.

Thanks

Chris

 

[baltman]

Add a singe quote to the front of your fields that you want to keep as text. e.g.: '000213 and it will be preserved as text.

Brian

 

[DEDMOD]

So exactly what are you using in your FoxPro code? You might need to put things in quotes or something to make Fox like it. Dave Dardinger

 

[cdms]

See what you think! It's fairly simple apart from the opentext()

********* EXCEL CODE START *********

#INCLUDE EXCEL9.H
#DEFINE FALSE .F.
#DEFINE TRUE .T.

TMPSHEET = GETOBJECT('','excel.sheet')

GEXCEL = TMPSHEET.APPLICATION

GEXCEL.VISIBLE = .T.

GEXCEL.WORKBOOKS.OPENTEXT("C:\vfp6tests\excel\SPRZ.TXT", XLWINDOWS, 1, XLDELIMITED, XLDOUBLEQUOTE,
FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, ARRAY(ARRAY(1,2), ARRAY(2,2), ARRAY(3, 1), ARRAY(4, 1),ARRAY(5, 1)))

********* EXCEL CODE END ***********

Appreciate the help

Chris

 

[DEDMOD]

I've got office 2002 and I can't find any Excel header files. Consequently when I try running your program I get "Variable XLWINDOWS not found". I think they must have changed things for Office 10, or was this header file one of your own construction?

At any rate, can you get me the values for XLWINDOWS, XLDELIMITED and XLDOUBLEQUPTE? Then I can test out the code. Dave Dardinger

 

[cdms]

Dave

Unfortunitly header files are not included in the office package BUT as it happens I do have a vfp program that helps with this problem.

As I'm sure you are aware, every office product ships with a .olb file which stores all the specific commands in that program. The following code extracts all the useful info out of the files and saves them as a .h file. Then all you have to do is include the file in forms or programs whenever you are automating office proucts.

****************START CODE****************
PUBLIC oform1

oform1=NEWOBJECT("form1&quot
oform1.SHOW
RETURN


****************FORM CODE****************
DEFINE CLASS form1 AS FORM

HEIGHT = 445
WIDTH = 567
DOCREATE = .T.
AUTOCENTER = .T.
BORDERSTYLE = 1
CAPTION = ".OLB Constants Extractor"
MAXBUTTON = .F.
MINBUTTON = .F.
NAME = "Form1"

ADD OBJECT txtolbfile AS TEXTBOX WITH ;
HEIGHT = 27, ;
LEFT = 65, ;
READONLY = .T., ;
TABINDEX = 2, ;
TOP = 6, ;
WIDTH = 458, ;
NAME = "txtOLBFILE"

ADD OBJECT label1 AS LABEL WITH ;
AUTOSIZE = .T., ;
CAPTION = &quot;.\<OLB File:&quot;, ;
HEIGHT = 17, ;
LEFT = 4, ;
TOP = 11, ;
WIDTH = 55, ;
TABINDEX = 1, ;
NAME = &quot;Label1&quot;

ADD OBJECT cmdsave AS COMMANDBUTTON WITH ;
TOP = 411, ;
LEFT = 394, ;
HEIGHT = 27, ;
WIDTH = 84, ;
CAPTION = &quot;\<Save to .h&quot;, ;
ENABLED = .F., ;
TABINDEX = 6, ;
NAME = &quot;cmdSAVE&quot;

ADD OBJECT cmdquit AS COMMANDBUTTON WITH ;
TOP = 411, ;
LEFT = 480, ;
HEIGHT = 27, ;
WIDTH = 84, ;
CAPTION = &quot;\<Quit&quot;, ;
TABINDEX = 7, ;
NAME = &quot;cmdQUIT&quot;

ADD OBJECT edtconstants AS EDITBOX WITH ;
HEIGHT = 347, ;
LEFT = 6, ;
READONLY = .T., ;
TABINDEX = 4, ;
TOP = 52, ;
WIDTH = 558, ;
NAME = &quot;edtConstants&quot;

ADD OBJECT cmdgetfile AS COMMANDBUTTON WITH ;
TOP = 6, ;
LEFT = 533, ;
HEIGHT = 27, ;
WIDTH = 26, ;
CAPTION = &quot;...&quot;, ;
TABINDEX = 3, ;
NAME = &quot;cmdGETFILE&quot;

ADD OBJECT cmdextract AS COMMANDBUTTON WITH ;
TOP = 411, ;
LEFT = 280, ;
HEIGHT = 27, ;
WIDTH = 110, ;
CAPTION = &quot;\<Extract Constants&quot;, ;
ENABLED = .F., ;
TABINDEX = 5, ;
NAME = &quot;cmdEXTRACT&quot;


PROCEDURE cmdsave.CLICK
STRTOFILE(THISFORM.edtconstants.VALUE,PUTFILE([Header File], ;
JUSTSTEM(THISFORM.txtolbfile.VALUE) + [.h],[.h]))
ENDPROC


PROCEDURE cmdquit.CLICK
THISFORM.RELEASE
ENDPROC


PROCEDURE cmdgetfile.CLICK
LOCAL lcOLBFile

lcOLBFile = GETFILE([OLB],[OLB File],[Open])
IF EMPTY(lcOLBFile)
RETURN .F.
ENDIF

IF UPPER(RIGHT(lcOLBFile,3)) # [OLB]
MESSAGEBOX([Invalid File],0,[])
RETURN .F.
ENDIF

THISFORM.txtolbfile.VALUE = lcOLBFile
THISFORM.cmdextract.ENABLED= .T.
ENDPROC


PROCEDURE cmdextract.CLICK
WAIT WINDOW [Processing...] NOCLEAR NOWAIT
LOCAL oTLB_INFO, oConstants, lcConstantsStr, Obj, member
#DEFINE CRLF CHR(13) + CHR(10)

oTLB_INFO = CREATEOBJECT([tli.typelibinfo])
oTLB_INFO.ContainingFile = (THISFORM.txtolbfile.VALUE)

oConstants = oTLB_INFO.Constants

lcConstantsStr = []
FOR EACH Obj IN oTLB_INFO.Constants
lcConstantsStr = lcConstantsStr + CRLF + &quot;* &quot; + Obj.Name + CRLF
FOR EACH member IN Obj.Members
lcConstantsStr = lcConstantsStr + [#DEFINE ] + ;
member.NAME + [ ] + ;
TRANSFORM(member.VALUE) + CRLF
NEXT member
NEXT Obj

THISFORM.edtconstants.VALUE=lcConstantsStr
THISFORM.cmdsave.ENABLED= .T.
WAIT CLEAR
WAIT WINDOW [Complete!] TIMEOUT 2
ENDPROC

ENDDEFINE
****************END CODE****************

Just copy this code into a program and run it.

Hope this helps

Chris

 

[wgcs]

A good way to look up Office constants is to use a Visual Basic project... just add a reference to Excel.Application, and then use the Object browser to find the constants. This also groups enumerations together, which makes, say, finding all the wdParagraphAlignment formats easy to find together even if they wouldn't sort together.

 

[DEDMOD]

Well, I managed to get the program to work after changing the ARRAYU(ARRAY) thingee by puting in

LOCAL xarray[5,2]
xarray(1,1) = 1
xarray(1,2) = 2
xarray(2,1) = 2
xarray(2,2) = 2
xarray(3,1) = 3
xarray(3,2) = 1
xarray(4,1) = 4
xarray(4,1) = 1
xarray(5,1) = 5
xarray(5,2) = 3

and then just putting 'xarray' (no quotes) in place of the array, but it didn't actually do anything. The text file I created was properly put into columns, but the ones which were supposed to be text were still imported as numbers. Using the wgcs suggestion I verified that 2 is the proper constant for the xltextformat. If I get a chance today I'll try changing the format values after the fact and see if it works. Dave Dardinger