There are a number of ways of accomplishing this task:
1. Export the Excel sheet(s) as comma delimited files (.CSV) and use the IMPORT command or the Import Wizard inside VFP to import the file(s).
2. Write custom VFP OLE automation code to automate Excel and extract the data to VFP.
3. Use the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) ODBC driver included with the 2007 Office System Driver: Data Connectivity Components package to access and extract data from the Excel workbook, either through a VFP Remote View or programmatically, as in the following sample code:
*-----------------------------------
* AUTHOR: Trevor Hancock
* CREATED: 02/15/08 04:55:31 PM
* ABSTRACT: Code demonstrates how to connect to
* and extract data from an Excel 2007 Workbook
* using the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
* from the 2007 Office System Driver: Data Connectivity Components
*-----------------------------------
LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
lcConnstr AS STRING
CLEAR
lcXLBook = [C:\SampleWorkbook.xlsx]
lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook
IF !FILE( lcXLBook )
? [Excel file not found]
RETURN .F.
ENDIF
*-- Attempt a connection to the .XLSX WorkBook.
*-- NOTE: If the specified workbook is not found,
*-- it will be created by this driver! You cannot rely on a
*-- connection failure - it will never fail. Ergo, success
*-- is not checked here. Used FILE() instead.
lnSQLHand = SQLSTRINGCONNECT( lcConnstr )
*-- Connect successful if we are here. Extract data...
lcSQLCmd = [Select * FROM "Sheet1$"]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
? laErr(3)
SQLDISCONNECT( lnSQLHand )
RETURN .F.
ENDIF
*-- Show the results
SELECT xlResults
BROWSE NOWAIT
SQLDISCONNECT( lnSQLHand )