Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Excel sheet
Message
From
06/06/2011 14:58:15
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Excel sheet
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows XP
Network:
Windows XP
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01513166
Message ID:
01513166
Views:
101
hi all,
i need help to add this code for all sheet
firstCell = sheetNames[m.ix]
secondCell = DATE()&&second line 
*******************************************
.Range("A1").value = m.firstCell
.Range("B1").value = m.secondCell 
LOCAL oExcel,ix,lcCursorName
LOCAL ARRAY sheetNames[18]
Select *;
    from ('NAMI22 ') WHERE office=1;
  into Cursor crsToExcel1 ;
  nofilter

Select *;
    from ('NAMI22 ') WHERE office=2;
  into Cursor crsToExcel2 ;
  nofilter
  
  Select *;
    from ('NAMI22 ') WHERE office=3;
  into Cursor crsToExcel3 ;
  nofilter

Select *;
    from ('NAMI22 ') WHERE office=5 ;
  into Cursor crsToExcel4 ;
  nofilter
  
  Select *;
    from ('NAMI22 ') WHERE office=6 ;
  into Cursor crsToExcel5 ;
  nofilter

Select *;
    from ('NAMI22 ') WHERE office=7 ;
  into Cursor crsToExcel6 ;
  nofilter
  
  Select *;
    from ('NAMI22 ') WHERE office=8 ;
  into Cursor crsToExcel7 ;
  nofilter

Select *;
    from ('NAMI22 ') WHERE office=9 ;
  into Cursor crsToExcel8 ;
  nofilter
  *************************
  
  Select *;
    from ('NAMI22 ') WHERE office=10 ;
  into Cursor crsToExcel9 ;
  nofilter

Select *;
    from ('NAMI22 ') WHERE office=13 ;
  into Cursor crsToExcel10 ;
  nofilter
  
 Select *;
from ('NAMI22 ') WHERE office=16 ;
into Cursor crsToExcel11 ;&&error message
nofilter

Select *;
from ('NAMI22 ') WHERE office=17  ;
into Cursor crsToExcel12 ;
nofilter

Select *;
from ('NAMI22 ') WHERE office=19  ;
into Cursor crsToExcel13 ;
nofilter

Select *;
from ('NAMI22 ') WHERE office=20  ;
into Cursor crsToExcel14 ;
nofilter

Select *;
from ('NAMI22 ') WHERE office=40  ;
into Cursor crsToExcel15 ;
nofilter

Select *;
from ('NAMI22 ') WHERE office=41  ;
into Cursor crsToExcel16 ;
nofilter

Select *;
from ('NAMI22 ') WHERE office=42  ;
into Cursor crsToExcel17 ;
nofilter

Select *;
from ('NAMI22 ') WHERE office=43  ;
into Cursor crsToExcel18 ;
nofilter

  ************************
sheetNames[1] = "SSSSS"
sheetNames[2] = "DDDDD"
sheetNames[3] = "GGGG"
sheetNames[4] = "HHHH"
sheetNames[5] = "HHHHHHHH"
sheetNames[6] = "RRRRRR"
sheetNames[7] = "GFHÉ"
sheetNames[8] = "GHJK"
sheetNames[9] = "DDDDD"
sheetNames[10] = "ZDXVBZXC"
sheetNames[11] = "ZZXDZFD"
sheetNames[12] = "ZSGZSFZFDSG"
sheetNames[13] = "SDZSZSZSGZS"
sheetNames[14] = "DZGZDFGZFZF"
sheetNames[15] = "ZDFGZDFZFDSGZDFG"
sheetNames[16] = "XXCZXCZ"
sheetNames[17] = "FDFDFDFDFD"
sheetNames[18] = "GFGFGFGFGFGFGF"

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
Next
Reply
Map
View

Click here to load this message in the networking platform