Using the Connectivity Kit to Connect to MS Excel Files

ID: Q116283

2.50 2.50a 2.50b 2.60 WINDOWS kbinterop The information in this article applies to:

Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
Microsoft FoxPro Connectivity Kit, versions 2.5, 2.6

SUMMARY

This article describes how to use the FoxPro Connectivity Kit and ODBC to access data that resides in a Microsoft Excel spreadsheet file.

MORE INFORMATION

This article assumes that you have the Microsoft Excel ODBC driver that is available as a part of the Microsoft ODBC Desktop Database Driver Kit (DDDK). This driver is also included with some Microsoft products, such as Microsoft Word for Windows and Microsoft Excel for Windows.

To connect to a Microsoft Excel data source, you must install the Microsoft Excel ODBC Driver and set up a Microsoft Excel data source through the ODBC Microsoft Excel Setup dialog box in the Windows Control Panel. Since this is a level 1 ODBC driver, the data source is a directory name or the current directory, as indicated in the Setup dialog box. The currently shipping Microsoft Excel ODBC driver supports only Microsoft Excel 4.0. The Microsoft Excel driver that ships with the ODBC 2.0 SDK provides an option in the Setup dialog box to specify Microsoft Excel versions 3.0 - 4.0 or Microsoft Excel version 5.0 files.

The following is a code sample that "connects" to a Microsoft Excel data source:

   *-- Connect to an MS Excel Data Source
   PRIVATE lcSource, lcUser, lcPwd, hDBHdle, lnDBRes

   *-- Init memvars
   STORE 0 TO hDBHdle, lnDBRes
   lcSource = "Excel"      && MS Excel Data Source Name from ODBC Dialog
   lcUser = ""          && Not needed for single tier drivers
   lcPwd = ""          && Not needed for MS Excel

   *-- Load Library if not already
   IF !"FPSQL" $ SET('library')
        SET LIBRARY TO FPSQL.FLL
   ENDIF
   WAIT WINDOW "Attempting connection ..." NOWAIT
   hDBHdle = dbConnect(lcSource, lcUser, lcPwd, "")
   IF hDBHdle > 0
      WAIT WINDOW ;
    "Connected to MS Excel File -  Handle:"+STR(hDBHdle,1,0) TIMEOUT 2
   ELSE
      WAIT WINDOW ;
    "Unable to connect to MS Excel File .."
   ENDIF

If the Microsoft Excel data source does not point to a specific directory (Use Current Directory is selected in the Setup dialog box), the following DBExec() function will place the user in the desired directory:

   lnDbRes = DBExec(hDBHdle, "USE f:\smpldata\Excel")
   *(change drive and directory to suit)

REFERENCES

Microsoft ODBC Desktop Database Driver Kit Excel Driver online Help file

Additional reference words: FoxWin 2.50 2.50a 2.50b 2.60 ck KBCategory: kbinterop KBSubcategory: FxtoolCk

Keywords          : kbcode FxtoolCk 
Version           : 2.50 2.50a 2.50b 2.60
Platform          : WINDOWS

Last Reviewed: May 22, 1998