Importing data from a Microsoft Excel 2007 workbook using Visual FoxPro 9.0

You have a Microsoft Excel 2007 workbook (.XLSX). You want to import data from it into a Microsoft Visual FoxPro (VFP) table using VFP.

 

Resolution

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 )

 

More Information

The 2007 Office System Driver: Data Connectivity Components package is available for download here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en)

 

DISCLAIMER

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.

 


APPLIES TO
Microsoft Visual FoxPro 9.0, Professional Edition
Microsoft Visual FoxPro 9.0 Service Pack 1
Microsoft Visual FoxPro 9.0 Service Pack 2
Microsoft Visual FoxPro 8.0 Professional Edition
Microsoft Visual FoxPro 7.0 Professional Edition
Microsoft Visual FoxPro 7.0 Service Pack 1
Microsoft Office Excel 2007

——————————————–

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
Microsoft Corporation. All rights reserved. Terms of Use | Trademarks


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image