>HI all
>
>I have generated a cursor of data taht I want to copy to a particular sheet of an XLS workbook starting from a certain cell. May I know how to do this in VFP9
>
>Your advice much appreciated.
>
>Thanks
>
>Best Regards
LOCAL oExcel,ix,lcCursorName
LOCAL ARRAY sheetNames[5]
Select emp_id,First_Name,Last_Name,;
Title,Notes ;
from (_samples+'\data\employee') ;
into Cursor crsToExcel1 ;
nofilter
Select cust_id,company,contact,Title,country ;
from (_samples+'\data\customer') ;
into Cursor crsToExcel2 ;
nofilter
Select * ;
from (_samples+'\data\orders') ;
into Cursor crsToExcel3 ;
nofilter
Select * ;
from (_samples+'\data\orditems') ;
into Cursor crsToExcel4 ;
nofilter
Select * ;
from (_samples+'\data\products') ;
into Cursor crsToExcel5 ;
nofilter
sheetNames[1] = "Employees"
sheetNames[2] = "Customers"
sheetNames[3] = "Orders"
sheetNames[4] = "Order Details"
sheetNames[5] = "Products"
oExcel = Createobject("Excel.Application")
With oExcel
.DisplayAlerts = .F.
.Workbooks.Add
.Visible = .T.
With .ActiveWorkBook
For ix = 1 To 5
lcCursorName = 'crsToExcel'+Ltrim(Str(m.ix))
If .sheets.Count < m.ix
.sheets.Add(,.sheets(.sheets.Count))
ENDIF
.WorkSheets(m.ix).Name = sheetNames[m.ix]
VFP2Excel(m.lcCursorName, .WorkSheets(m.ix),"A1" )
ENDFOR
.WorkSheets(1).Activate
Endwith
Endwith
Function VFP2Excel
Lparameters tcCursorName, toSheet, tcTargetRange
tcCursorName = Iif(Empty(m.tcCursorName),Alias(),m.tcCursorName)
tcTargetRange = Iif(Empty(m.tcTargetRange),'A1',m.tcTargetRange)
Local loConn As AdoDB.Connection, loRS As AdoDB.Recordset,;
lcTempRs, lcTemp, oExcel
lcTemp = Forcepath(Sys(2015)+'.dbf',Sys(2023))
lcTempRs = Forcepath(Sys(2015)+'.rst',Sys(2023))
Select (m.tcCursorName)
Copy To (m.lcTemp)
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+Sys(2023)
loConn.Open()
loRS = loConn.Execute("select * from "+m.lcTemp)
loRS.Save(m.lcTempRs)
loRS.Close
loConn.Close
Erase (m.lcTemp)
loRS.Open(m.lcTempRs)
With toSheet
.QueryTables.Add( loRS, .Range(m.tcTargetRange)).Refresh()
Endwith
loRS.Close
Erase (m.lcTempRs)
Cetin