>>Agnes,
>>.Value = getArrayRef("myArray")
>>
>>is one the fastest methods I have ever seen (I timed them all - 200K+ rows in multiple sheets). However, with array approach I found that excel may choke (depending on excel version, RAM ...), it was hard to find the rowcount that you can send at once.
>
>We never used Excel 2K7 to its limits (rows) With 2K3 we calculate the number of rows on Sheet can receive (there may be uper and lower limits, we fill XLT based)
>There was never a failure. Our RAM is normaly 4G, We supported only Excel W2K3 and W2K7 yet. (We do deeper stuff with automation, customer restricts its comp to this so no need for testing other versions)


I experienced the 'choke' on 2K3. I calculate too. I am not sure if that was not Excel's fault or not, I needed to lower the rowcount transferred at once (when I tested them of course RAM sizes were not yet at Gb level - probably the test machine had 192 Mb back then.)

>
>BTW how would one linit the connection to a range?


Init connection to a range? I am not sure what you mean. You can use named ranges, like naming a range as 'myData' in a template xlt file. Then it would look like:

.Range('myData')...


>What we found is trouble with date values. Some user enters 1.4.0083 and this brokes the array method. It stops at the cell with this date.

With VFPOLEDB you shouldn't have that problem, didn't test explicitly. Sample code handles empty dates.


>>.QueryTables.Add(...) is nice but if you save the sheet and move to another PC (no connection to data IOW) then reopening is slow (waits for timing out trying to locate and connect to source). Otherwise nice it loads and updates from source.
>
>I read that CopyFromRecordSet will insert values only. No connection will remain in the XLS.

Yes that is true. That is why I prefer CopyFromRecordSet vs Querytables.Add



>>From a cursor it works by copying out to tables (you are passing connection and SQL). In case of free tables you don't have a problem other than fieldname (and that is not a problem because you are free to write whatever you want for headers).
>
>No idea how this looks like. Can you provide a code snippet?

* These represent complex SQL as a sample
Select emp_id,First_Name,Last_Name,;
  Title,Notes ;
  from (_samples+'\data\employee') ;
  into Cursor crsEmployee ;
  readwrite
Replace All Notes With Chrtran(Notes,Chr(13)+Chr(10),Chr(10))

Select cust_id,company,contact,Title,country,postalcode ;
  from (_samples+'\data\customer') ;
  into Cursor crsCustomer ;
  nofilter

Select * ;
  from (_samples+'\data\orders') ;
  into Cursor crsOrders ;
  nofilter

Select * ;
  from (_samples+'\data\orditems') ;
  into Cursor crsOrderDetail ;
  nofilter

Select * ;
  from (_samples+'\data\products') ;
  into Cursor crsProducts ;
  nofilter

* Now we want to get these on 3 sheets
* Sheet1: Employees only
* Sheet2: Customers only
* Sheet3: Orders, ordItems, Products layed out horizontally

Local oExcel
oExcel = Createobject("Excel.Application")
With oExcel
  .DisplayAlerts = .F.
  .Workbooks.Add
  .Visible = .T.
  With .ActiveWorkBook
    For ix = 1 To 3 && We want 3 Sheets
      If .sheets.Count < m.ix
        .sheets.Add(,.sheets(.sheets.Count)) && Add new sheets
      Endif
    Endfor
    * Name the sheets
    .WorkSheets(1).Name = "Employees"
    .WorkSheets(2).Name = "Customers"
    .WorkSheets(3).Name = "Order, OrderDetail, Products" && max sheetname is 31 chars

    * Start sending data
    * First one has headers specified
    VFP2Excel('crsEmployee',    .WorkSheets(1).Range("A1"), ;
      "Id,First Name,Last Name,Employee Title,Comments about employee" ) && To sheet1, start at A1
    VFP2Excel('crsCustomer',    .WorkSheets(2).Range("A1") ) && To sheet2, start at A1
    VFP2Excel('crsOrders',      .WorkSheets(3).Range("A1") ) && To sheet3, start at A1
    * Need to know where to put next
    * Leave 2 columns empty - something like 'G1'
    lcRange = _GetChar(.WorkSheets(3).UsedRange.Columns.Count + 3) + '1'
    * To sheet3, start at next to previous
    VFP2Excel('crsOrderDetail', .WorkSheets(3).Range(m.lcRange) )

    lcRange = _GetChar(.WorkSheets(3).UsedRange.Columns.Count + 3) + '1'
    * To sheet3, start at next to previous
    VFP2Excel('crsProducts',    .WorkSheets(3).Range(m.lcRange) )

    #Define xlJustify                                         -4130
    #Define xlTop                                             -4160

    * I just happen to know notes in at column 5 from SQL
    * No need to query from excel to keep code simple
    * Lets format that column specially instead of leaving
    * at the mercy of Excel's autofitting
    .WorkSheets(1).UsedRange.VerticalAlignment = xlTop && set all to top
    With .WorkSheets(1).Columns(5)
      .ColumnWidth = 80 && 80 chars width
      .WrapText = .T.
      *      .HorizontalAlignment = xlJustify && doesn't work good always
    Endwith

    * Finally some cosmetic stuff
    For ix=1 To 3
      With .WorkSheets(m.ix)
        .Columns.AutoFit
        .Rows.AutoFit
      Endwith
    Endfor

    .WorkSheets(1).Activate
  Endwith
  #Define xlWorkbookNormal                                  -4143
  * for another test save in current Excel version
  .ActiveWorkBook.SaveAs('c:\temp\ReadMeBack.xls',xlWorkbookNormal)
Endwith


* Warning:
* Copying to a free table (might be dbc based)
* temporarily and using field names as headers
* if not passed.
* This might lead to problems like:
* Truncated fieldnames of 2 columns collide and copy to errors
* Field names might be cryptic for the enduser
Function VFP2Excel
  Lparameters tcCursorName, toRange, tcHeaders
  tcCursorName = Iif(Empty(m.tcCursorName),Alias(),m.tcCursorName)
  Local loConn As AdoDB.Connection, loRS As AdoDB.Recordset,;
    lcTemp, oExcel,ix

  lcTemp = Forcepath(Sys(2015)+'.dbf',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)

  * Use first row for headers
  Local Array aHeader[1]

  toRange.Offset(1,0).CopyFromRecordSet( loRS )  && Copy data starting from headerrow + 1
  For ix=1 To Iif( !Empty(m.tcHeaders), ;
      ALINES(aHeader, m.tcHeaders,1,','), ;
      loRS.Fields.Count )
    toRange.Offset(0,m.ix-1).Value = ;
      Iif( !Empty(m.tcHeaders), ;
      aHeader[m.ix], ;
      Proper(loRS.Fields(m.ix-1).Name) )
    toRange.Offset(0,m.ix-1).Font.Bold = .T.
  Endfor

  loRS.Close
  loConn.Close
  Erase (m.lcTemp)
Endfunc

* Return A, AA, BC etc noation for nth column
Function _GetChar
  Lparameters tnColumn && Convert tnvalue to Excel alpha notation
  If m.tnColumn = 0
    Return ""
  Endif
  If m.tnColumn <= 26
    Return Chr(Asc("A")-1+m.tnColumn)
  Else
    Return 	_GetChar(Int(Iif(m.tnColumn % 26 = 0,m.tnColumn - 1, m.tnColumn) / 26)) + ;
      _GetChar((m.tnColumn-1)%26+1)
  Endif
Endfunc

Cetin