do excel2vfp with fullpath('fms1.xls'),.t.,.t.,.t.

* Foxite
* Thread 153776
* Author : Cetin Basoz

PROCEDURE EXCEL2VFP
LPARAMETERS lcDataSource,lFirstRowHasHeader,lReturnAllAsText,ll2007
#Define adSchemaCatalogs 1
#Define adSchemaColumns 4
#Define adSchemaTables 20
Close Databases All
Local lcConStr


IF NOT ll2007
* 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')+'"'
else
* Excel2007
      lcConStr = ;
        'Provider=Microsoft.ACE.OLEDB.12.0;'+;
        'Data Source='+m.lcDataSource+';'+;
        'Extended Properties="Excel 12.0;HDR='+;
        IIF(m.lFirstRowHasHeader,'Yes','No')+'"'
ENDIF

ReadSchema(m.lcConStr)
Select TableList
Scan
  lcTableName = Trim(Table_name)
  lcLocalCursor = Textmerge("Sheet >")
  ADOQuery(m.lcConStr, Textmerge("select * from [ >]"), 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