locked
Export from Visual Foxpro application to excel 2007? RRS feed

  • 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

    Monday, July 9, 2007 2:36 AM

Answers

  • 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.

     

    Monday, July 9, 2007 11:40 AM
  • 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

    Monday, July 9, 2007 3:24 PM

All replies

  • 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.

     

    Monday, July 9, 2007 11:40 AM
  • 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

    Monday, July 9, 2007 3:24 PM