Microsoft KB Archive/120870

= XL: Overview of ODBC Add-in Functions and Arguments =

Article ID: 120870

Article Last Modified on 8/16/2005

-

APPLIES TO


 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 5.0 for Macintosh

-



This article was previously published under Q120870





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)

<pre class="fixed_text">  SQL.RETRIEVE(connection_num,destination_ref,max_columns,max_rows,   col_names_logical,row_nums_logical,named_rng_logical,fetch_first_logica   l)

<pre class="fixed_text">  SQLRetrieve(ConnectionNum,DestinationRef,MaxColumns,MaxRows,   ColNamesLogical,RowNumsLogical,NamedRngLogical,FetchFirstLogical)

<pre class="fixed_text">  SQL.RETRIEVE.TO.FILE(connection_num,destination,col_names_logical,   column_delimiter)

<pre class="fixed_text">  SQLRetrieveToFile(ConnectionNum,Destination,ColNamesLogical,   ColumnDelimiter)

NOTE: The order of the arguments in the Microsoft Excel 4.0 function SQL.REQUEST differs from that of the Visual Basic function SQLRequest.

NOTE: Although the argument names differ slightly between the Microsoft Excel 4.0 macro functions and the Visual Basic functions, the arguments themselves have the same meaning. For example, the ConnectionNum argument for a Visual Basic function has the same meaning as connection_num for a Microsoft Excel version 4.0 macro language function.

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

<pre class="fixed_text">  Argument             Description -

<pre class="fixed_text">  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.

<pre class="fixed_text">  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.

<pre class="fixed_text">  OutputRef            A cell reference where you want the completed output_ref          connection string placed.

<pre class="fixed_text">  QualifierText        A string that qualifies the information returned qualifier_text      by TypeNum--only for a TypeNum of 3, 4, or 5.

<pre class="fixed_text">  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.

<div class="references_section">