Export VFP dbf to Excel

Question

How can I export a VFP dbf into a spreadsheet in Excel?  There are 2 columns that have memo data, will that open in Excel as well?

Answer 1

There are many samples here. ie:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2185983&SiteID=1

 

Answer 2

And here is a single table with memo sample:

 

Code Snippet

#Define xlTop -4160
Local oExcel
oExcel = Createobject("Excel.Application")
With oExcel
  .WorkBooks.Add
  .Visible = .T.
  VFP2Excel(_samples+'data\testdata.dbc',;
    'select emp_id,first_name,last_name,'+;

    'CAST(CHRTRAN(notes,CHR(13),"") as M) as Notes from employee',;
    .ActiveWorkBook.ActiveSheet.Range('A1'))
   
  * This part is cosmetic 
  With .ActiveWorkBook.ActiveSheet
    .UsedRange.VerticalAlignment = xlTop && set all to top
    With .Columns(5)
      .ColumnWidth = 80 && 80 chars width
      .WrapText = .T.
    Endwith
    .UsedRange.Columns.AutoFit
    .UsedRange.Rows.AutoFit
  Endwith
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
Endfunc

 

 

Answer 3

This is excellent, Cetin!

 

Answer 4

Thanks Dan