Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Yousfi Benameur
  Where is Yousfi Benameur?
 El Bayadh
 Algeria
 Yousfi Benameur
 To: Jonel Villanueva
  Where is Jonel Villanueva?
 sta. rosa
 Philippines
 Jonel Villanueva
 Tags
Subject: RE: Convert Data into Excel File Report
Thread ID: 209491 Message ID: 209559 # Views: 2 # Ratings: 0
Version: Visual FoxPro 8 Category: General VFP Topics
Date: Monday, December 29, 2008 3:17:55 PM         
   


> Good Day!
>
> My first question.
>
> 1.I have data to be converted to excel report. is there any existing sample here in foxite? this is my first time to do this
> kind of reports.
>
> 2. VFP9 can be run in Win VISTA?
>
> Happy New year,
>
> Thanks,
>
> Jonel

Hi Jonel
here a code from Cetin to convert table (even with memo) to excel

#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.Rows(1).Orientation = -70
    .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

adapt to your data

this is another code
*convert any table with memo or not to excel
*Anders Altberg  and Yousfi Benameur    Thursday 07/24/2008

afile=getfile('dbf')
af1=justfname(afile)
path=justpath(afile)
***********************
LOCAL oExcel AS Excel.APPLICATION
  LOCAL oBook AS Excel.Workbook
  LOCAL oSheet AS OBJECT
  oExcel = CREATEOBJECT("Excel.Application")
  oBook = oExcel.Workbooks.ADD
  oSheet = oBook.Worksheets(1)
  
 *Create the QueryTable object.
  LOCAL oQryTable AS OBJECT, strData AS STRING
  strData = path                    
  xx= 'oSheet.QueryTables.ADD("OLEDB;Provider=VFPOLEDB.1;Data Source="+ strData+";", oSheet.RANGE("A1"),"Select * from '+af1+'")'
  oQryTable=evaluate(xx)
  oQryTable.RefreshStyle = 2 && xlInsertEntireRows = 2
  oQryTable.REFRESH(.F.)
  oExcel.VISIBLE=.T.



regards

ENTIRE THREAD

Convert Data into Excel File Report Posted by Jonel Villanueva @ 12/29/2008 10:06:22 AM
RE: Convert Data into Excel File Report Posted by Stefan Wuebbe @ 12/29/2008 10:15:04 AM
RE: Convert Data into Excel File Report Posted by Jonel Villanueva @ 12/29/2008 10:22:43 AM
RE: Convert Data into Excel File Report Posted by Stefan Wuebbe @ 12/29/2008 10:29:05 AM
RE: Convert Data into Excel File Report Posted by Jonel Villanueva @ 12/29/2008 12:39:13 PM
RE: Convert Data into Excel File Report Posted by Stefan Wuebbe @ 12/29/2008 5:58:04 PM
RE: Convert Data into Excel File Report Posted by Jonel Villanueva @ 12/30/2008 4:51:15 AM
RE: Convert Data into Excel File Report Posted by Yousfi Benameur @ 12/29/2008 3:17:55 PM
RE: Convert Data into Excel File Report Posted by Jonel Villanueva @ 12/30/2008 4:55:57 AM