Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: mohammed qasem
  Where is mohammed qasem?
 amman
 Jordan
 mohammed qasem
 Tags
Subject: convert to excel
Thread ID: 209386 Message ID: 209386 # Views: 16 # Ratings: 0
Version: Visual FoxPro 9 Category: Forms
Date: Sunday, December 28, 2008 9:00:54 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

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