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


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

Hi yousfi,

thanks for this info. i will try this one.


happy new year..


jonel

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