Incorporating Excel

[bebbo]

I have a table which holds membership information. This information needs extracting into an Excel Spreadsheet. Presently this is achieved by extracting this table into a txt file then opening Excel and opening this text file within Excel. Is there a way this can be achieved within a Foxpro program without seperately opening Excel. If not surely there is an easier way to extract data into Excel???

 

[csimion]

You should try

EXPORT TO FILENAME.XLS TYPE XLS

It should be easyer.
Good Luck!

 

[Mike Gagnon]

SELECT myTable
COPY to myExcel TYPE XLS (XL5)

Or you could automate the whole process, if you need proper formatting

 

[bebbo]

Thanks

I've know been told that the operator has got to be able to select what fields should be exported. Is there an easy way just to export certain fields and allow the operator to pick these fields???

 

[rgbean]

Both COPY TO and EXPORT support a list of Fields. Just store the field names in a character variable, and include a macro to expand them in the command. e.g.

lcFields = "Name, SSNO, Data1"
EXPORT TO FILENAME.XLS TYPE XLS FIELDS &lcFields

Rick

 

[bebbo]

Thanks

That has worked like a treat. One little thing the filds headings appear on the first line of the Excel file. Is it possible to remove this line or even better is it possible to have these as the Excel headings (i.e. Firstname instead of A, Secondname instead of B, etc.....).

 

[wgcs]

Not automatically... you could:

EXPORT TO the export file
loEx = CREATEOBJECT('excel.application')
use loEx to open the file
automate through loEx to set the headings and remove the first row.

 

[jaa69]

Yes you can will take one more step but should be quite easy.

Use a select statement to select the fields you want then export the query to Excel as stated above

select field1 as LastName, field2 as FirstName, field3 as City into cursor newtable

select newtable

export to filename.xls type xls(5)

This will extract the fields you ask for plus change the Headings so they are easier to work with.

 

[baltman]

If you "copy fields x,y,z to output.csv type delim" the file will proably be set to open with Excel (it seems to register itself as master of the 'csv' extension) and there will be no headers.

Brian