> Cetin,
> If you have any suggestions as to how to append to a DBF using OLEDB, it would be much appreciated.
>
> An alternative solution to my problem is...
> If you know how to use automation to save in 95 format, that could be used as well.
> (for some reason, if you save an 2007Excel file in 95.xls format, it works under foxPro... but not if saved under 97/2003.xls, or its native 2007.xlsx format)
>
> file1 = C:\temp\green.xls
> file2 = C:\temp\white.xls
>
> I am able to save as 97 format by using:
> oworkbook.SaveAs(file1)
>
> But am unable to convert using:
> copy file (file1) to (file2) TYPE XL5
>
> Thanks again
> Chris

Chris,
Multiple questions that is, hope I won't miss anyone while replying:)

Append to a dbf - that's from Excel to VFP. For excel you can use ODBC/OleDB driver to read excel tables (Excel tables are table like areas on a sheet and need not be starting from upper left corner. Besides sheets with 'tables' there system tables. I don't know how would you eliminate those system tables during getting the data as cursors but as long as you know sheet names to get the data from you can check sheet name vs table name. Excel 'tables' are named like SheetName$, may contain spaces and limited to 31 in length).

OK now that be a long post.

Read excel tables into cursors (here it's a quick version that simply names cursors as sheet01,..sheetNN):

#Define adSchemaCatalogs 1
#Define adSchemaColumns 4
#Define adSchemaTables 20
Close Databases All
Local lFirstRowHasHeader,lReturnAllAsText,lcDataSource,lcConStr
lFirstRowHasHeader = .T.
lReturnAllAsText = .F.

lcDataSource = 'c:\temp\ReadMeBack.xls'
*lcDataSource = 'c:\temp\ReadMeBack.xlsx'

* Excel97-2003
lcConStr = ;
  'Provider=Microsoft.Jet.OLEDB.4.0;'+;
  'Data Source='+m.lcDataSource+';'+;
  'Extended Properties="Excel 8.0;HDR='+;
  IIF(m.lFirstRowHasHeader,'Yes','No')+';IMEX='+;
  IIF(m.lReturnAllAsText,'1','0')+'"'

* Excel2007
*lcConStr = ;
  'Provider=Microsoft.ACE.OLEDB.12.0;'+;
  'Data Source='+m.lcDataSource+';'+;
  'Extended Properties="Excel 12.0;HDR='+;
  IIF(m.lFirstRowHasHeader,'Yes','No')+'"'

ReadSchema(m.lcConStr)
Select TableList
Scan
  lcTableName = Trim(Table_name)
  lcLocalCursor = Textmerge("Sheet<<PADL(RECNO(),2,'0')>>")
  ADOQuery(m.lcConStr, Textmerge("select * from [<<m.lcTableName>>]"), m.lcLocalCursor)
Endscan

Procedure ADOQuery(tcConStr,tcQuery,tcCursorName)
  Local oConn As 'ADODB.Connection'
  Local oRS As ADODB.RecordSet
  oConn = Createobject('ADODB.Connection')
  oConn.Mode= 1  && adModeRead
  oConn.Open( m.tcConStr )
  oRS = oConn.Execute(m.tcQuery)
  RS2Cursor(oRS,m.tcCursorName)
  oRS.Close
  oConn.Close
Endproc

Procedure ReadSchema(tcConStr)
  Local oConn As 'ADODB.Connection'
  Local rstSchema As ADODB.RecordSet
  oConn = Createobject('ADODB.Connection')
  oConn.Mode = 1  && adModeRead
  oConn.Open( m.tcConStr )
  rstSchema = oConn.OpenSchema(adSchemaTables)
  *rstSchema = oConn.OpenSchema(adSchemaColumns)
  RS2Cursor(rstSchema,'TableList')
  rstSchema.Close
  oConn.Close
Endproc

Procedure RS2Cursor(toRS, tcCursorName) && simple single cursor - not intended for complex ones
  tcCursorName = Iif(Empty(m.tcCursorName),'ADORs',m.tcCursorName)
  Local xDOM As 'MSXML.DOMDocument'
  xDOM = Createobject('MSXML.DOMDocument')
  toRS.Save(xDOM, 1)
  Xmltocursor(xDOM.XML, m.tcCursorName)
Endproc

To create the data to use in sample you could use OLEDB again, this time from VFP to Excel (my favorite method BTW over any other export to Excel):

* 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

> An alternative solution to my problem is...
> If you know how to use automation to save in 95 format, that could be used as well.
> (for some reason, if you save an 2007Excel file in 95.xls format, it works under foxPro... but not if saved under 97/2003.xls, or its native 2007.xlsx format)
>
> file1 = C:\temp\green.xls
> file2 = C:\temp\white.xls
>
> I am able to save as 97 format by using:
> oworkbook.SaveAs(file1)
>

VFP doesn't really know formats later than 5 and I even doubt it knows 5 (somewhere I have demo code to make it crash with 5 format as well. BUT that might have been corrected, I haven't used Import or append from with excel for years).
Anyway if you want to save in a old format like 5:

#define xlExcel9795 43
lcXLS = getfile('XLS')
oExcel = createobject('Excel.Application')
with oExcel
 .DisplayAlerts = .f.
 .WorkBooks.Open( m.lcXLS )
 .SaveAs(m.lcXLS, xlExcel9795)
 .Quit
endwith

> But am unable to convert using:
> copy file (file1) to (file2) TYPE XL5

AFAIK the only supported 'copy' with type XLS5 is:

use someTable
copy to (m.lcXLS) type xl5

Beware it could fail, plus it doesn't do its job well under all versions of VFP. Better alternatives:
1)OLEDB copying (as shown above)
2)

copy to (m.lcXLS) type CSV

3)

copy to (m.lcXLS) type fox2x

4)

copy to (m.lcXLS) type delimited

&& this one is followed by automation oExcel.OpenText(...).
5)Write out the table as HTML (textmerge) and open with excel
6)Like 5 but XML. Some Excel versions do not understand and need some automation.
7)Full XML creation of xls file (complex IMHO if you don't have libraries ready).

Cetin Basoz