<< Click to Display Table of Contents >> Navigation:  Examples > Other samples > 4: Jet & Excel

This sample is designed to show you how to create and open Excel files using the Ms Jet provider.

 

FUNCTION Start()

 LOCAL oConn AS AdoConnection

 LOCAL oRs        AS AdoRecordSet

 LOCAL cExcel        AS STRING

 LOCAL nCount AS DWORD

 cExcel := "C:\Vo2Ado26\ExcelData.XLS"

 IF ! FILE(cExcel)

 // Create Excel file

 oConn := AdoConnection{}

 oConn:ConnectionString := "Provider=Microsoft.Jet.OLEDB.4.0;" + ;

            "Data Source=c:\Vo2Ado26\Northwind.mdb"

 oConn:Open(NIL,NIL,NIL,NIL)

 oConn:Execute('Select * Into Customers ' + ;

                  ' IN "'+cExcel+'" "Excel 8.0;" '+ ;

                  ' From Customers',NIL,NIL)

 oConn:Close()

 ENDIF

 // Open Excel Database

 oConn := AdoConnection{}

       oConn:ConnectionString := "Provider=Microsoft.Jet.OLEDB.4.0;" + ;

                  "Data Source="+cExcel+";" + ;

                  'Extended Properties="Excel 8.0;HDR=Yes"'

 

 // Display list of tables in Excel Database

 // Note that it shows both a Worksheet name and a range name

 // The name that you need to open the data is the Range name (without the $ sign)

 oConn:Open(NIL,NIL,NIL,NIL)

 oRs := oConn:OpenSchema(adSchemaTables,NIL,NIL)

 DO WHILE ! oRs:EOF

 ? oRs:[Collect,"TABLE_NAME"]

 oRs:MOveNext()

 ENDDO

 oRs:Close()

 // Now open specific table

 oRs:Open("Customers", oConn,NIL,NIL,NIL)

 DO WHILE ! oRs:EOF .and. ++nCount < 10

 ? oRs:[Collect,1], oRs:[Collect,2], oRs:[Collect,3]

 oRs:MOveNext()

 ENDDO

 oRs:close()

 oConn:Close()

 wait