Export data to multiple Excel sheets?

Question

How can I export data from Visual FoxPro into Excel file I know. I something like this:

USE tableName
EXPORT TO (fileName) TYPE XL5 AS CPDBF()

I get Excel file with one sheet. Does anybody know how can export second table to the same excel file but in different sheet? I prefer Visual FoxPro code but you can write me example in C#, for example how to export each data table to different Excel sheet but in the same Excel file.

Kind regards, Ozren Sirola

Answer 1

You can't do that with the EXPORT or COPY TO commands. To put data into multiple sheets in Excel, you need to use Automation. The fastest approach is probably to use EXPORT or COPY TO to create multiple workbooks, and then use Automation to consolidate the data into a single workbook.

The Automation part would look something like:

oXL = CREATEOBJECT("Excel.Application")
oBook = oXL.Workbooks.Open("<the file containing the sheet you want first>")
* Copy second sheet to first workbook
oBook2 = oXL.Workbooks.Open("<the file containing the sheet you want second>")
oBook2.Sheets[1].Copy(, oBook.Sheets[1])
oBook2.Close()
* Copy third sheet to first workbook
oBook2 = oXL.Workbooks.Open("<the file containing the sheet you want third>")
oBook2.Sheets[1].Copy(, oBook.Sheets[2])
oBook2.Close()
* Etc.

oBook.Save()
oBook.Close()
oXL.Quit()