XL: Macro Examples Using XLODBC Functions

ID: Q124218


The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a


SUMMARY

In the versions of Microsoft Excel mentioned at the beginning of this article, you can use the Microsoft ODBC add-in for Microsoft Excel (XLODBC.XLA) to create macros that interact directly with ODBC through the ODBC driver manager. XLODBC.XLA contains Visual Basic for Applications functions as well as Microsoft Excel version 4.0 macro language functions.

This article provides examples using Visual Basic for Applications functions.

Note that XLODBC.XLA does not use Microsoft Query to retrieve data through ODBC (This behavior is different from the Microsoft Query add-in, XLQUERY.XLA).


MORE INFORMATION

The following table lists the XLODBC.XLA Visual Basic functions.


This function            Does the following
----------------------------------------------------------------------
SQLBind                  Specifies storage for a result column

SQLClose                 Closes a data source connection

SQLError                 Returns detailed error information

SQLExecQuery             Sends a query to a data source

SQLGetSchema             Gets information about a connected data source

SQLOpen                  Establishes a connection to a data source

SQLRetrieve              Retrieves query results

SQLRetrieveToFile        Retrieves query results and places them in a file

SQLRequest               Connects with an external data source, performs
                         the query, and returns the result set 

Tips for Using the XLODBC.XLA Functions

  • When you use these functions, add XLODBC.XLA as a library reference in your workbook. If XLODBC.XLA is not added, you may receive the error message "Sub or function not defined" when you try to run an XLODBC.XLA function. To add XLODBC.XLA as a library reference, do the following:

    1. In a Visual Basic module, click References on the Tools menu.


    2. In the References dialog box, click to select the XLODBC.XLA check box.


    NOTE: If XLODBC.XLA is not present in the References dialog box, add it by clicking the Browse button and locating XLODBC.XLA. With Microsoft Excel versions 5.0 and 7.0, the add-in is in the EXCEL\LIBRARY\MSQUERY folder. With Microsoft Excel 97, the add-in is in the \PROGRAM FILES\MICROSOFT OFFICE\OFFICE\LIBRARY\MSQUERY folder. With Microsoft Excel for the Macintosh, version 5.0, the XLODBC.XLA add-in is located in the Microsoft Office:Office:MS Query folder.


  • If you are using long SQL statements with an XLODBC.XLA function, you can pass the SQL statement as type Variant rather than as type String. For information about how to do this, see Sample 8 in the "Visual Basic Code" section of this article.


  • If you are having difficulties with a particular SQL statement and a data source, try running the query in Microsoft Query to determine if it will run successfully there.


  • If you are writing an application that uses the XLODBC.XLA functions, set the XLODBC.XLA functions to Variant data type variables because they can potentially return Error data types. For example, if you set SQLOpen to a variable of type Integer and a connection cannot be made to the specified data source, you could receive the run-time error "Type Mismatch." Also, use the SQLError function to determine errors returned from an XLODBC.XLA function. (See Sample 3 later in this article for more information about how to use SQLError.)


Using the Sample Macros

Most of the examples in this article use a sample data source called "NWIND." To successfully use these examples, you must create a dBASE IV data source named NWIND that uses the sample dBASE IV files included with Microsoft Excel.
  • With Microsoft Excel 5.0, the sample dBASE IV files are located in \WINDOWS\MSAPPS\MSQUERY\.


  • With Microsoft Excel 7.0, the sample dBASE IV files are located in \PROGRAM FILES\COMMON FILES\MICROSOFT SHARED\MSQUERY\.


  • With Microsoft Excel 97, the sample dBASE IV files are located in \PROGRAM FILES\MICROSOFT OFFICE\OFFICE\.


Sample Visual Basic Code

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
Sample 1: Using SQLOpen to Connect to an External Data Source and to Determine a Connection String
The following example demonstrates how you can use SQLOpen to connect to a data source and then determine the connection string. When you run this sample code, the "Select Data Source" dialog box is displayed to allow you to select a data source. After you have selected a data source, the connection string for the established connection will be returned to the active cell on the active sheet. To use this example, do the following:

  1. Enter the following macro code in a module sheet:
    
    Sub UsingSQLOpen()
    
                       Dim Chan As Variant
    
                       ' Establish a connection to any data source and place
                       ' the connection string to the selected data source in
                       ' the active cell.
                       Chan = SQLOpen("", ActiveCell)
    
                       ' Close the connection to the selected data source.
                       SQLClose (Chan)
    
                    End Sub
     


  2. Activate an empty worksheet and run the UsingSQLOpen macro.

    If the macro is successful, you will be prompted to select a data source. Select any data source and the macro will place the connection string in the active cell on the active sheet.


Sample 2: Using SQLRetrieve to Return External Data to a Worksheet
The following example queries the Orders table in the NWind data source and returns the data to the active sheet starting at cell A1. This sample macro queries the ORDERS.DBF table to retrieve the ORDER_ID and CUSTMR_ID values, where EMPLOY_ID is equal to '555'. To use this example, do the following:

  1. Enter the following code in a module sheet:
    
                   Sub RetrieveData()
    
                       Dim Chan As Variant
    
                       ' Establish a connection to the NWind data source.
                       Chan = SQLOpen("DSN=Nwind")
    
                       ' Execute a query to retrieve the ORDER_ID and CUSTMR_ID
                       ' from the Orders table where EMPLOY_ID is 555.
                       SQLExecQuery Chan, _
                        "SELECT Order_id, Custmr_id FROM Orders.dbf WHERE " & _
                        "Employ_id='555'"
    
                       ' Return the data to cell A1 on the active sheet.
                       SQLRetrieve Chan, ActiveSheet.Range("A1"), , , True
    
                       ' Close the connection to the NWind data source.
                       SQLClose (Chan)
    
                    End Sub 


  2. Activate an empty worksheet and run the RetrieveData macro.

    If the macro is run successfully, two columns of data will be returned to the active sheet at cell A1.


Sample 3: Using SQLError to Report Errors from XLODBC.XLA Functions
The following example demonstrates how you can use SQLError to report errors that may be encountered while you use XLODBC.XLA commands. The SQLOpen in this example will return an error if you do not have an ODBC data source named "Just Testing." If an error is encountered, the function SQLError will return a variant array. The variant array will contain one row for each error generated and each row will contain the following:

  • A character string indicating the ODBC error class and subclass.


  • A numeric value indicating the data source native error code.


  • A text message describing the error.


If there are no errors from a previous ODBC function call, this function returns only the #N/A error value. To use this example, do the following:

  1. Enter the following two macros in a module sheet:
    
                    Sub XLODBCErrHandler()
    
                       Dim ErrMsgs As Variant
                       Dim ErrCode As Variant
    
                       ' Call SQLError to return error values to the variant
                       ' ErrMsgs.
                       ErrMsgs = SQLError()
    
                       ' Display each item in the ErrMsgs variant array.
                       For Each ErrCode In ErrMsgs
                          MsgBox ErrCode
                       Next
    
                    End Sub
    
                    Sub ProduceError()
    
                       Dim Chan As Variant
    
                       ' Establish a connection to a nonexistent data source.
                       Chan = SQLOpen("DSN=Just Testing")
    
                       ' If there was a problem establishing a connection,
                       ' SQLOpen will return an error to the variable Chan. If
                       ' Chan is an error then call the procedure
                       ' XLODBCErrHandler to display the error message.
    
                       If IsError(Chan) Then
                          XLODBCErrHandler
                          Exit Sub
                       End If
    
                       ' Close the connection if one was established.
                       SQLClose (Chan)
    
                    End Sub 


  2. b. Run the ProduceError macro.


If a connection cannot be made to the data source "Just Testing," dialog boxes will be displayed to describe the error that was encountered while attempting to connect to the data source.
Sample 4: Using SQLGetSchema to Get Table Names in a Database
The following example uses SQLGetSchema to determine the name of the database or selected folder on the connection to the NWind data source. Then, once you know the name of the database or selected folder, you can use SQLGetSchema once more to determine the names of all the tables in the database or selected folder. To use this example, do the following:
  1. Enter the following macro code in a module sheet:
    
                    Sub GetTableNames()
    
                       Dim Chan As Variant
                       Dim TableNames, TName As Variant
                       Dim DatabaseName As Variant
    
                       ' Establish a connection to the NWind data source.
                       Chan = SQLOpen("DSN=NWind")
    
                       ' Get the name of the database on the connection
                       DatabaseName = SQLGetSchema(Chan, 7)
    
                       ' Get the table names for the database on the
                       ' connection.
                       TableNames = SQLGetSchema(Chan, 4, DatabaseName & ".")
    
                       ' Display the name of each table.
                       For Each TName In TableNames
                         MsgBox TName
                       Next TName
    
                       ' Close the connection to the NWind data source.
                       SQLClose (Chan)
    
                    End Sub 


  2. Run the GetTableNames macro.

    If this macro is run successfully, dialog boxes will be displayed containing the name of each sample dBASE table.


Sample 5: Using SQLGetSchema to Get the Field Names in a Table
The following example uses SQLGetSchema to retrieve the field names for each field in the ORDERS table in the Nwind Data Source. To use this example, do the following:
  1. Enter the following macro code in a module sheet:
    
                    Sub GetFieldNames()
    
                       Dim Chan As Variant
                       Dim Fields As Variant
                       Dim i As Integer
    
                       ' Establish a connection to the NWind data source.
                       Chan = SQLOpen("DSN=NWind")
    
                       ' Get the field names for the ORDERS table.
                       Fields = SQLGetSchema(Chan, 5, "ORDERS")
    
                       ' Fields is a two-dimension Variant array; each row
                       ' contains field name and the field type. Display an
                       ' alert message for each row to show each field name.
    
                       For i = 1 To UBound(Fields)
                          MsgBox Fields(i, 1)
                       Next i
    
                       ' Close the connection.
                       SQLClose (Chan)
    
                    End Sub 


  2. Run the GetFieldNames macro.

    If the macro runs successfully, a dialog box will be displayed for each field name in the ORDERS table.


Sample 6: Using SQLBind to Specify Where Data Will Be Returned to a Worksheet
The following example runs a query to return three columns from the Orders table in Nwind. The three fields (Order_ID, Custmr_ID, and Order_Amt) are then bound to specific columns in the active sheet. Order_ID is bound to column A, Custmr_ID is bound to column D and Order_Amt is bound to column B. To use this example, do the following:
  1. Enter the following code in a module sheet:
    
                    Sub UsingSQLBind()
     
                       Dim Chan As Variant
                       Dim NumRows, NumCols As Variant
     
                       ' Establish a connection to the NWind data source.
                       Chan = SQLOpen("DSN=NWind")
     
                       ' Run a query to the Orders table to retrieve the
                       ' fields: Order_ID, Custmr_ID, Order_Amt.
     
                       NumCols = SQLExecQuery(Chan, _
                          "SELECT Order_ID, Custmr_ID, " & _
                          "Order_Amt FROM Orders.Dbf")
     
                       ' Bind the first column (Order_ID) to cell A1 of the
                       ' Activesheet, Bind the second column (Custmr_ID) to
                       ' cell D1 of the Activesheet, Bind the third column
                       ' (Order_Amt) to cell B1 of the active sheet.
                       SQLBind Chan, 1, Range("A1")
                       SQLBind Chan, 2, Range("D1")
                       SQLBind Chan, 3, Range("B1")
     
                       ' Return the data and the field names.
     
                       NumRows = SQLRetrieve(Chan, , , , True)
     
                       ' Close the connection to the NWind data source.
                       SQLClose (Chan)
     
                    End Sub 


  2. Activate an empty worksheet, and then run the UsingSQLBind macro.


Sample 7: Retrieve Records to an Array using SQLRequest
You can use the SQLRequest function to return records into an array. The following example demonstrates how to query a table and return the result set to an array. To use this example, do the following:
  1. Enter the following code in a module sheet:
    
                    Sub ReturnDataToArray()
     
                       Dim EmpNames As Variant
     
                       ' Query the EMPLOYEE table in the NWind data source for
                       ' the first and last name of each employee and return
                       ' the result set to the variant array EmpNames.
     
                       EmpNames = SQLRequest("DSN=NWind", _
                          "Select Last_Name, First_Name from EMPLOYEE")
     
                       ' For each row in the array Empnames, display the first
                       ' and last name of the employee.
     
                       For i = 1 To UBound(EmpNames, 1)
                          MsgBox EmpNames(i, 1) & "," & EmpNames(i, 2)
                       Next
     
                    End Sub 


  2. Run the ReturnDataToArray macro.

    If the macro is run successfully, a dialog box will be displayed containing the first and last name of each employee in the EMPLOYEE.DBF table.


Sample 8: Using Long SQL Query Strings
NOTE: There is a maximum limit of 255 characters in a SQL query string. You may also receive GPFs if the length exceeds 127 characters. Use the method below to workaround these issues. If you use a long SQL query, you can choose to pass the SQL query to SQLRetrieve or SQLRequest as a Variant data type rather than a String data type. The following example demonstrates how this can be done. To use this example, do the following:
  1. Enter the following code on a module sheet:
    
                    Function StringToArray(Query As String) As Variant
     
                       Const StrLen = 127 ' Set the maximum string length for
                                          ' each element in the array to return
                                          ' to 127 characters.
                       Dim NumElems As Integer
                       Dim Temp() As String
     
                       ' Divide the length of the string Query by StrLen and
                       ' add 1 to determine how many elements the String array
                       ' Temp should contain, and redimension the Temp array to
                       ' contain this number of elements.
     
                       NumElems = (Len(Query) / StrLen) + 1
                       ReDim Temp(1 To NumElems) As String
     
                       ' Build the Temp array by sequentially extracting 127
                       ' segments of the Query string into each element of the
                       ' Temp array.
     
                       For i = 1 To NumElems
                          Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
                       Next i
     
                       ' Set the function StringToArray to the Temp array so it
                       ' can be returned to the calling procedure.
     
                       StringToArray = Temp
     
                    End Function
     
                    Sub ExecuteLongSQL()
     
                       Dim Chan As Variant
                       Dim LongSQL As String
                       Dim NumRows, NumCols As Variant
     
                       ' Set LongSQL to a long query (> 127 characters).
                       ' The following statement should be entered as one
                       ' single line.
     
                       LongSQL = "SELECT CUSTMR_ID, EMPLOY_ID, ORDER_AMT, " &_
                        "ORDER_DATE, ORDER_ID FROM ORDERS WHERE EMPLOY_ID=" _
                        & "'555' AND ORDER_AMT>=100"
     
                       ' Establish a connection to the data source NWind.
                       Chan = SQLOpen("DSN=NWind")
     
                       ' Execute the long query statement by passing
                       ' SQLExecQuery, a variant array which is returned from
                       ' the function StringToArray.
     
                       NumCols = SQLExecQuery(Chan, StringToArray(LongSQL))
     
                       ' Return the data to the Active cell on the Active
                       ' sheet.
                       NumRows = SQLRetrieve(Chan, ActiveCell)
     
                       ' Close the channel established to NWind.
                       SQLClose Chan
     
                    End Sub 


  2. Activate an empty worksheet and then run the ExecuteLongSQL macro.


Sample 9: How to Execute a Stored Procedure Using SQLExecQuery

             Sub ExecuteStoredProcedure()
 
             Dim Chan As Variant
 
             'Open a channel to a SQL Server data source
             Chan = SQLOpen("DSN=SQLServer")
 
             'Execute the stored procedure "sp_who" and return the
             'results to the activecell on the active sheet.
             SQLExecQuery Chan, "Execute sp_who"
 
             SQLRetrieve Chan, ActiveCell
 
             'Terminate the channel
             SQLClose Chan
 
             End Sub 


REFERENCES

"Microsoft Excel User's Guide," Chapter 23

For more information on a specific XLODBC.XLA function, click the Search button in Visual Basic Reference Help, type the appropriate function name, and click the Show Topics button.

For information about the SQL query 255 character limit, please see the following article in the Microsoft Knowledge Base:

Q114992 GP Fault in XLODBC.DLL Using SQLExecQuery or SQLRequest

Additional query words: 5.00a 5.00c 7.00a greater larger odbc xl97 xl vba

Keywords : kbprg kbualink97 kbdta kbdtacode PgmOthr PgmHowto KbVBA
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,97; Win95:7.0,7.0a
Platform : MACINTOSH Win95 WINDOWS
Issue type : kbhowto


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.