How do you save to a Excel .xlsx file

[hkrawitz]

It is easy to save to a .xls file but I can't seem to find a way to save to an .xlsx file. With the ability to save larger files to the .xlsx files this would be a great savings. Any help would be appreciated.

I am using VFP 9.0

Thanks

 

[JRB-Bldr]

The short answer is - You cannot save directly from VFP to an XLSX type of file.

But you can save to a CSV file and then use Excel Automation to open the CSV and then save it as an XLSX file.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

You can also automate excel to load xls and save as xlsx.
What is most viable also depends on how many rows you have.

See here, there is quite some discussion (and even a quarrel). In the end take what suits you best.

thread184-1667260

Bye, Olaf.

 

[Mike Lewis]

Do you have a special reason for wanting to export to XLSX rather than XLS? All versions of Excel can open and read XLS files, whereas only 2007 and later can natively handle XLSX.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[hkrawitz]

Hi All,

Thanks for your help. The reason I wanted to go direct to xls is i work with very large files in the 100s of thousands so when I finish a job that the customer wants back in excel I would typically copy the file to ie: copy to smith.xls type xls. The problem is only 65K records will come across.

My option is to save it to a text file and then import it into an xlsx file. My hopes were there was some update out there to save the extra time it takes to do the work. I know it doesn't sound like a lot but when you are processing millions of records every second counts.

Thanks everyone.

Howard

 

[Olaf Doschke]

No, there is no such update.

As marcopolo summarised going throuh text (perhaps HTML, as in mm0000's code) is your only chance to process so much rows.

You could rather use VFPs oledb provider from Excel 2007 and load on data. But I assume you do have your result in a cursor and so would first need to write it out as a dbf, some time penalty involved.

Bye, Olaf.

 

[endhey]

You Can Try This Code :

PUBLIC oExcel, oBook, oSheet

oExcel = CREATEOBJECT("Excel.Application")
oExcel.Visible = .T.

oBook = oExcel.Workbooks.Add()
oSheet = oBook.Sheets[1]

=SQLEXEC(mConDB,"SELECT * FROM Table","cDetail")

SELECT cDetail
GOTO TOP
x=3

DO WHILE !EOF()
i=i+1
oSheet.Range('A'+ALLTRIM(STR(i))).value=Field1
oSheet.Range('B'+ALLTRIM(STR(i))).value=Field2
oSheet.Range('C'+ALLTRIM(STR(i))).value=Field3
oSheet.Range('D'+ALLTRIM(STR(i))).value=Field4
oSheet.Range('E'+ALLTRIM(STR(i))).value=Field5
SKIP
ENDDO

cFile="D:\Excel\FileName"
oExcel.ActiveWorkbook.SaveAs (cFile)

SELECT cDetail
USE

Note : MS. Office 2007 or ++ already installed

 

[IlyaRabyy]

I would strongly recommend not to make the oExcel, oBook and oSheet PUBLIC memvars, let alone use implicit declaration of the m.I and m.X memvars, colelague Endhey! Especially since you do not RELEASE either of them at the end of your code (and do not even use that m.X after assigning the Int value to it).

I would declare all the memvars for the Excel's objects, as well as the counter m.I memvar, as LOCALs... and, BTW, I'd close/exit those Excel objects at the end, too. [;-)]

Regards,

Ilya

 

[hkrawitz]

Thanks for your assistance. I appreciate everyones help.

Thanks

Howard