-1

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

Class Skeleton
  • 2,913
  • 6
  • 31
  • 51
Shixx
  • 49
  • 7

1 Answers1

3

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()
Tamar E. Granor
  • 3,817
  • 1
  • 21
  • 29
  • This was previous quick solution. Now I have new one which uses VFP2Excel function which can create "n" Sheets from one big cursor which have some DISTINCT atribut from which you want create "n" Sheets in your Excel. To do that I am using now this "COPY TO excel Sheet in foxpro" article on StackOverflow which is one of the best solutions for Visual FoxPro tasks which need Excel Automation and creation of Excel with Sheets. – Shixx Jan 20 '17 at 11:32