none
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?

    Monday, April 21, 2008 9:30 PM

Answers

  • 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

     

     

    Tuesday, April 22, 2008 8:50 AM

All replies

  • Tuesday, April 22, 2008 12:01 AM
  • 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

     

     

    Tuesday, April 22, 2008 8:50 AM
  • This is excellent, Cetin!

     

    Tuesday, April 22, 2008 6:40 PM
  • Thanks DanSmile

     

     

    Wednesday, April 23, 2008 9:38 PM