Export from Visual Foxpro application to excel 2007?

Question

Hi,

 

I'm trying to export huge amount of data from visual foxpro application to excel.

Since the old version of excel can only handles up to 65536 rows, so I upgraded to excel 2007.

But after I exported to excel 2007, it still show me 65536 rows only instead of 1M rows.

Why it's still using the xls instead of xlsx?

 

Thanks in advance.

 

Best regards,

Yong Ling

Answer 1

The Excel drivers that come with Visual FoxPro are old. They do not support newer versions of Excel so you are limited in the number of rows you can export.

 

You can always use an intermediate file format (like CSV, or DBF) and then pick it from Excel.

 

Answer 2

Excel can open csv files natively. IMHO best way to export data to Excel is using ADO transfers. ie:

 

LOCAL oExcel

oExcel = Createobject("Excel.Application")

With oExcel

  .WorkBooks.Add

 .Visible = .T.

 VFP2Excel(_samples+'data\testdata.dbc','select * from customer',.ActiveSheet.Range('A1'))

Endwith

 

function VFP2Excel

lparameters tcDataSource, tcSQL, toRange

Local loConn As AdoDB.Connection, ;

loRS As AdoDB.Recordset,ix

loConn = Createobject("Adodb.connection")

loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource

loConn.Open()

loRS = loConn.Execute(m.tcSQL)

FOR ix=1 TO loRS.Fields.Count

  toRange.Offset(0,m.ix-1).Value = PROPER(loRs.Fields(m.ix-1).Name)

    toRange.Offset(0,m.ix-1).Font.Bold = .t.

ENDFOR

toRange.Offset(1,0).CopyFromRecordSet( loRS )

loRs.Close

loConn.Close