Show TOC

Procedure documentationUsing Microsoft Excel Locate this document in the navigation structure

 

The following procedure describes how you can use Microsoft Excel 2003 or Microsoft Excel 2007 as a data source.

Prerequisites

You have created a source system of the type OLE DB Agent.

Procedure

  1. Select the OLE DB source system in the PCo Console.

    The system displays the OLE DB Connection tab.

  2. Choose the Configure Connection String pushbutton.

    The Data Link Properties dialog box appears.

  3. Click the Provider tab and choose the appropriate entry:

    Data Source Used

    Provider

    Excel 2003 (*.xls)

    Microsoft Jet 4.0 OLE DB Provider

    Excel 2007 (*.xlsx)

    Microsoft Office 12.0 Access Database Engine OLE DB Provider

  4. Choose the Next >> function key or click the Connection tab.

    The Connection tab page appears.

  5. On the Connection tab, enter the directory where the Excel file is located; for example, C:\tmp\Test.xls.

    If you use the browser key (...) , you must specify the file type All Files (*.*) so that the Excel file is displayed. You can ignore the user name and the password.

  6. Choose the Advanced tab to specify the access authorizations:

    • Read

      In the Access Permissions area, select the Read entry if you only want to assign a read authorization.

    • ReadWrite

      Select ReadWrite if you want to grant a read and write authorization.

  7. Choose the All tab. Double-click the Extended Properties entry.

    The Edit Property Value dialog box appears.

  8. Enter the appropriate value in the Property Value field.

    Data Source Used

    Property Value

    Excel 2003 (*.xls)

    Excel 8.0;HDR=Yes;IMEX=1

    Excel 2007 (*.xlsx)

    Excel 8.0;HDR=YES

    Note Note

    If your Excel sheet does not contain a header line, enter HDR=NO.

    End of the note.

  9. On the All tab, check that False has been set for the Persist Security Info entry.

  10. On the Connection tab page, choose the Test Connection function key to test the connection to the Excel file.

    If the connection works, the system sends a success message.

  11. Choose the OK function key to adopt the settings.

    The dialog box is closed.

  12. Choose Save.