Importing to Excel

Dear Friends,

I would like to export my cursors to Excel file but when I do this they send only the datas of one cursor and the rest not. How to do this

eg ;

SELECT cursor1

SELECT cursor2

SELECT cursor3

copy to compare.xls type xls

only the datas of the cursor3 will be exported not for the rest

advance thanks for your suggestions

satish

Visual FoxPro1

"SELECT" select only one table at time.So, vfp consider the last select (SELECT 3).You need to export to excel after each select.

thanks

Friend, when we export after each select what happens is we again get the last selects details , I need them all in the same spreadsheet otherwise in the same excel sheet so my question is how to collect all the cursors details in the same sheet.

thanks

satish

1. You may fill a array with data from cursors and then export in excel format.

2.You may export in 3 excel diffent files and join them with excel(you can make a macro).

Satish,

The COPY TO command only operates on the currently selected work area. If your 3 cursors are all the same structure you can use a SQL-SELECT to create a single file:

select * from cursor1 ;
into cursor4 ;
union ;
select * from cursor2 ;
union ;
select * from cursor3

select cursor4
copy to TheFile.xls type xl5

Copy to xls is almost useless. Here is a sample using ADO instead.

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

lnSheets = 3 && we need 3 at least

oExcel = Createobject("Excel.Application")
With oExcel
.DisplayAlerts = .F.
.Workbooks.Add
.Visible = .T.
With .ActiveWorkBook
If .sheets.Count < m.lnSheets
.sheets.Add(,.sheets(.sheets.Count),;
m.lnSheets - .sheets.Count) && Add new sheets after old ones
Endif

VFP2Excel('crsToExcel1', .WorkSheets(1),"A1" )
VFP2Excel('crsToExcel2', .WorkSheets(2),"A1" )
VFP2Excel('crsToExcel3', .WorkSheets(3),"A1" )
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)

Dear Friends,

Sorry for the late response.

Thanks for all your kind help I learnt a lot thanks again.

satish