XL: Overview of ODBC Add-in Functions and Arguments

• Microsoft Excel for Windows, version 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Macintosh, version 5.0

SUMMARY

In the versions of Microsoft Excel listed at the beginning of this article, the XLODBC.XLA ODBC add-in provides Open Database Connectivity (ODBC) functions that allow you to connect to external data sources directly. XLODBC.XLA provides functions in both the Visual Basic for Applications and Microsoft Excel 4.0 macro languages. All of the functions are macro commands with the exception of SQL.REQUEST and SqlRequest, which are worksheet functions.

NOTE: The functions SQLBind, SQLClose, SQLError, SQLExec, SQLGetSchema, SQLOpen, SQLRetrieve, and SQLRetrieveToFile are displayed in the Function Wizard when the active sheet is a worksheet. However, these functions can be used only in a macro or module sheet.

MORE INFORMATION

The following is a list of the functions provided by XLODBC.XLA and a description of the arguments for those functions. The arguments for each function are shown here as they are displayed in the Function Wizard.

Note that there are some discrepancies in the argument naming conventions used between the Function Wizard and the Help files.

   SQL.BIND(connection_num,column,reference)
   
   SQLBind(ConnectionNum,Column,Reference)
   
   SQL.CLOSE(connection_num)
   
   SQLClose(ConnectionNum)
   
   SQL.ERROR()
   
   SQLError()
   
   SQL.EXEC.QUERY(connection_num,query_text)
   
   SQLExecQuery(ConnectionNum,QueryText)
   
   SQL.GET.SCHEMA(connection_num,type_num,qualifier_text)
   
   SQLGetSchema(ConnectionNum,TypeNum,QualifierText)
   
   SQL.OPEN(connection_string,output_ref,driver_prompt)
   
   SQLOpen(ConnectionStr,OutputRef,DriverPrompt)
   SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_
   names_logical)
   SQLRequest(ConnectionStr,QueryText,OutputRef,DriverPrompt,ColNamesLogi
   cal)
   
   SQL.RETRIEVE(connection_num,destination_ref,max_columns,max_rows,
   col_names_logical,row_nums_logical,named_rng_logical,fetch_first_logica
   l)
   
   SQLRetrieve(ConnectionNum,DestinationRef,MaxColumns,MaxRows,
   ColNamesLogical,RowNumsLogical,NamedRngLogical,FetchFirstLogical)
   
   SQL.RETRIEVE.TO.FILE(connection_num,destination,col_names_logical,
   column_delimiter)
   
   SQLRetrieveToFile(ConnectionNum,Destination,ColNamesLogical,
   ColumnDelimiter) The order of the arguments in the Microsoft Excel 4.0
   function SQL.REQUEST differs from that of the Visual Basic function
   SQLRequest.

The following is a description of the arguments used in the above functions:

   Argument             Description
   ---------------------------------------------------------------------
   
   ColNamesLogical      True if you want column names returned as the
   col_names_logical    first row of the results.
   
   Column               The number of the result column to be bound.
   column               Result columns are numbered from left to right
                        starting with 1. Column 0 provides row numbers.
                        If you omit column, all bindings for the
                        connection are removed.
   
   ColumnDelimiter      The string to use to separate the elements in
   column_delimiter     each row of the created text file. If omitted,
                        a tab is used.
   
   ConnectionNum        The unique ID returned by SQLOpen for the
   connection_num       connection made to the data source.

   ConnectionStr        Supplies information, such as the data source
   connection_string    name, user ID, and password, required by the
                        driver being used to connect to a data source.
                        Must follow the driver's format (see the
   "References"
                        section of this article for information about
                        where the driver's help file is located). Strings
                        longer than 255 characters must be broken into
                        an array of strings.
   
   Destination          A string that specifies the name and path of
   destination          the file where you want to place the results.
                        An existing file will be overwritten.
   
   DestinationRef       A reference specifying the top-left cell where
   destination_ref      results should be placed. Any previous values
                        in the destination cells are overwritten
                        without confirmation. If omitted, results are
                        placed according to previous calls to SQLBind.

   DriverPrompt         A number specifying if and how the driver should
   driver_prompt        prompt for additional needed information for a
                        connection. Refer to the online help for values.
   
   FetchFirstLogical    Important only if MaxRows is specified for
   fetch_first_logical  SQLRetrieve, and there are more rows of results
                        than MaxRows available. Use True to return
                        the first set of rows from the beginning of a
                        query's results. Then call SQLRetrieve repeatedly
                        using False to return the next set of rows until
                        all result rows are returned. Refer to the online
                        help for details.
   
   MaxColumns           The maximum number of columns to be returned to
   max_columns          the worksheet starting at DestinationRef.
                        If omitted, all columns are returned.
   
   MaxRows              The maximum number of rows to be returned to
   max_rows             the worksheet starting at DestinationRef.
                        If omitted, all rows are returned.
   
   NamedRngLogical      True if you want each column of the results to
   named_rng_logical    be declared as a named range on the worksheet.
   
   OutputRef            A cell reference where you want the completed
   output_ref           connection string placed.
   
   QualifierText        A string that qualifies the information returned
   qualifier_text       by TypeNum--only for a TypeNum of 3, 4, or 5.

   QueryText            The SQL query to be executed on the data source.
   query_text           Strings longer than 255 characters must be broken
                        into an array of strings. While common queries use
                        a standard syntax, there may be differences in
                        driver limitations and extensions (see the
                        "References" section of this article for
   information
                        about where the driver's help file is located).
   
   Reference            The location of a single cell on a worksheet where
   reference            you want the results bound as a Range object.
                        If Reference is omitted, binding is removed for
                        the column.
   
   RowNumsLogical       Used only when destination is included in the
   row_nums_logical     function call. If rowNumbers is True, the
                        first column in the result set contains row
                        numbers. If destination is False or omitted,
                        the row numbers are not returned.

   TypeNum              Specifies the type of information you want
   type_num             returned. Refer to the online help for values.

REFERENCES

For more information about individual functions, see the appropriate Help file. If you are using Microsoft Windows, the ODBC driver Help files are located in the Windows\System directory and are named DRVXXXXX.HLP. If you are using a Macintosh, the Help files for Microsoft ODBC drivers are located in the Microsoft folder and are called "ODBC SQL Server Help" and "Q+E ODBC FoxPro Driver Help."

"Microsoft Excel 5.0 User's Guide," Chapter 23.