Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Excel sheet
Message
From
07/06/2011 20:11:39
Hilmar Zonneveld
Independent Consultant
Cochabamba, Bolivia
 
 
To
06/06/2011 14:58:15
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows XP
Network:
Windows XP
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01513166
Message ID:
01513471
Views:
45
What help do you need? What isn't working?

>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
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform