Exporting dbf file to MS Excel in different sheets

Thanks for your fast response,  I've heard about Macro but never tried it before on foxpro.
Where can I find a tutorial for this method?

It is hard to know with the code you have posted, at what point you need to decide when you want to put the data on another sheet. Whats is the criteria? The record count perhaps?.
There aren't any tutorial (that I know of) on how to use Excel Macro in VFP. It is mostly a matter of a little translation. For example if you start Excel, and go to Tools-Macro-Record a new Macro, and add an extra sheet to your workbook. Stop the macro recording, and go back to the Macro and edit it. You will find :
>>Sheets.Add

Which is not enough to get you going, but knowing the rest of the code may help (Automate Excel, add a new workbook etc...), so here is how you would put that together in VFP.

oExcel = CREATEOBJECT('Excel.application')
oWb = oExcel.Workbooks.Add()
oSheet = owb.Sheets.Add  && That is the Macro part.
oExcel.Visible = .t.

Hi Mikegagnon,

Below are the simplify code to give you an idea on how its going to be.
Here I've made a select statement on a selected field (PLU, ITEM, QTY and DEPT).
DEPT will content a department code which consist 1-9.  
Here I've already filtered department 2 and inserted into table ENG3.
Then with this table ENG3 that has been filtered, is exported to Menu.xls.

Now for my question is, as I filtered out the other departments,
I would like to use the same Menu.xls file and put it into different sheets.
How should I program it to perform this action?


SELECT ENG2.PLU,ENG2.ITEM,ENG2.QTY,ENG2.DEPT,
    FROM D:\ENG2,
    WHERE ENG2.DEPT=2;      
INTO TABLE D:\ENG3

CLOSE DATABASES

USE D:\ENG3

COPY TO D:\Menu TYPE XL5

Ok, now I understand. But copy to will not do it for you (Unless you want to create a separate workbook for each departments - where you would use an SQL statement filtering on a particular department and using copy to). I am affraid your solution lies in automating Excel as I have shown you above. The solution is more complex than one moght think. Here is the scenario.
1. Do an SQL statement as you have above.
2. Do a second DISTINCT sql statement to determine how many departments you have to deal with and loop through the second cursor to filter out the departments on the first cursor.
3. Once you have you third cursor, loop through each record, inserting line by line the records.
4. Once you have done the first department, add a new page to the workbook and start working on the second department on the new page.
5. Keep going on the same process for all individual departments.

The code for this is difficutl to post as each field of your table need to be formatted depending on the field type.

Here is a start if you wish to open a new workbook for each table, cut is and paste it on a separate sheet of the original workbook.

cFileName = SYS(5)+SYS(2003) + '\temp\test1.dbf'
oexcel = CREATEOBJECT("Excel.Application")
oexcel.Visible=.t.
oWBAll = oexcel.Workbooks.Add
oWBDBF = oexcel.Workbooks.Open(cFilename)
oWBDBF.Sheets(1).Cells.Cut
oSheet = oWBAll.Sheets.Add
oSheet.Paste
oWBDBF.Close(.F.)

oWBDBF = temporary workbook for the dbf
oWBAll contains all sheets

Make sure you have the tables in fox2x format as I mentioned earlier since excel would not open the latest dbf version.

Thanks to all for your suggestion... I'll try to see if it works...

Hi experts,

I've tried out the sample code you've given, there  are 2 problems I'm currently facing:
1. while executing the script,  a pop-up message appears saying "File is in Use"
2. Looking at the Excel file, all the data are stored in one column.