Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: mohammed qasem
  Where is mohammed qasem?
 amman
 Jordan
 mohammed qasem
 Tags
Subject: RE: convert to excel
Thread ID: 209386 Message ID: 209390 # Views: 8 # Ratings: 1
Version: Visual FoxPro 9 Category: Forms
Date: Sunday, December 28, 2008 10:00:08 AM         
   


> hi all,
>
> i try to convert tables to excel sheets , i get error message Alias'crstoexcel11' is not found
>
>
> LOCAL oExcel,ix,lcCursorName
> LOCAL ARRAY sheetNames[18]
> Select *;
>     from ('t1') ;
>   into Cursor crsToExcel1 ;
>   nofilter
> 
> Select *;
>     from ('t2') ;
>   into Cursor crsToExcel2 ;
>   nofilter
>   
>   Select *;
>     from ('t3') ;
>   into Cursor crsToExcel3 ;
>   nofilter
> 
> Select *;
>     from ('t4') ;
>   into Cursor crsToExcel4 ;
>   nofilter
>   
>   Select *;
>     from ('t5') ;
>   into Cursor crsToExcel5 ;
>   nofilter
> 
> Select *;
>     from ('t6') ;
>   into Cursor crsToExcel6 ;
>   nofilter
>   
>   Select *;
>     from ('t7') ;
>   into Cursor crsToExcel7 ;
>   nofilter
> 
> Select *;
>     from ('t8') ;
>   into Cursor crsToExcel8 ;
>   nofilter
>   *************************
>   
>   Select *;
>     from ('t9') ;
>   into Cursor crsToExcel9 ;
>   nofilter
> 
> Select *;
>     from ('t10') ;
>   into Cursor crsToExcel10 ;
>   nofilter
>   
>   Select *;
>     from ('t13') ;
>   into Cursor crsToExcel13 ;&&error message
>   nofilter
> 
> Select *;
>     from ('t16') ;
>   into Cursor crsToExcel16 ;
>   nofilter
>   
>   Select *;
>     from ('t17') ;
>   into Cursor crsToExcel17 ;
>   nofilter
> 
> Select *;
>     from ('t19') ;
>   into Cursor crsToExcel19 ;
>   nofilter
>   
>   Select *;
>     from ('t40') ;
>   into Cursor crsToExcel40 ;
>   nofilter
> 
> Select *;
>     from ('t41') ;
>   into Cursor crsToExcel41 ;
>   nofilter
>   
>   Select *;
>     from ('t42') ;
>   into Cursor crsToExcel42 ;
>   nofilter
> 
> Select *;
>     from ('t43') ;
>   into Cursor crsToExcel43 ;
>   nofilter
>   ************************
> sheetNames[1] = "Employees"
> sheetNames[2] = "Customers"
> sheetNames[3] = "Orders"
> sheetNames[4] = "Order Details"
> sheetNames[5] = "Products"
> sheetNames[6] = "eEmployees"
> sheetNames[7] = "eCustomers"
> sheetNames[8] = "eOrders"
> sheetNames[9] = "eOrder Details"
> sheetNames[10] = "eProducts"
> sheetNames[11] = "rEmployees"
> sheetNames[12] = "rCustomers"
> sheetNames[13] = "rOrders"
> sheetNames[14] = "rOrder Details"
> sheetNames[15] = "rProducts"
> sheetNames[16] = "tEmployees"
> sheetNames[17] = "tCustomers"
> sheetNames[18] = "tOrders"
> 
> 
> oExcel = Createobject("Excel.Application")
> With oExcel
>   .DisplayAlerts = .F.
>   .Workbooks.Add
>   .Visible = .T.
>   With .ActiveWorkBook
>     For ix = 1 To 18
>       lcCursorName = 'crsToExcel'+Ltrim(Str(m.ix))
>       If .sheets.Count < m.ix
>         .sheets.Add(,.sheets(.sheets.Count)) && Add new sheet
>       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)
> 
> 

>
> thanks

You need cursors to be named from crsToExcel1 to crsToExcel18 in the for loop. So rename the cursors in the select statement

Select *;
from ('t13') ;
into Cursor crsToExcel11 ;&&error message
nofilter

Select *;
from ('t16') ;
into Cursor crsToExcel12 ;
nofilter

Select *;
from ('t17') ;
into Cursor crsToExcel13 ;
nofilter

Select *;
from ('t19') ;
into Cursor crsToExcel14 ;
nofilter

Select *;
from ('t40') ;
into Cursor crsToExcel15 ;
nofilter

Select *;
from ('t41') ;
into Cursor crsToExcel16 ;
nofilter

Select *;
from ('t42') ;
into Cursor crsToExcel17 ;
nofilter

Select *;
from ('t43') ;
into Cursor crsToExcel18 ;
nofilter

Regards
Tushar

ENTIRE THREAD

convert to excel Posted by mohammed qasem @ 12/28/2008 9:00:54 AM
RE: convert to excel Posted by tushar @ 12/28/2008 10:00:08 AM
RE: convert to excel Posted by Anders Altberg @ 12/29/2008 5:32:00 PM