XL5: Using DB.SET.DATABASE and Q+E Macro Functions in MS Excel

• Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel 5.0, when you run a macro created in an earlier version of Microsoft Excel that contains the DB.SET.DATABASE function or any other Q+E command-equivalent function, the macro may not work correctly.

CAUSE

In order to use a macro that contains Q+E command-equivalent functions, you must have one of the following add-in macros loaded:

    - Q+E Add-in (the 5.0 version of the add-in)

    NOTE: If you use this version of the add-in, you must verify that the
    macro refers to a valid ODBC data source (for more information about
    referring to a valid data source, see Method 2 in the "Resolutions"
    section of this article).

    - Q+E Integration Add-in (the earlier version of the add-in)

RESOLUTIONS

To ensure the proper behavior of DB.SET.DATABASE and the other Q+E macro functions, use either of the following methods before you run a macro that contains Q+E command-equivalent functions.

Microsoft provides macro 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 macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

The methods in this section use the following sample macro to better illustrate the resolutions. Create this sample macro in Microsoft Excel version 3.0 or 4.0 (and make sure to specify the correct path to your .DBF file in the DB.SET.DATABASE statement). The sample macro refers to the EMPLOYEE.DBF file in the C:\ directory.

Sample Macro

=SELECT("R1C2") =DB.SET.DATABASE(FALSE,"dBASEFile",{"c:\employee.dbf"}) =DB.SQL.QUERY(2,"select employ_id from dBASEFile|employee.dbf",1,,FALSE) =DB.PASTE.FIELDNAMES(,{"EMPLOY_ID"}) =SET.EXTRACT() =DB.EXTRACT(FALSE,1,,FALSE) =RETURN()

This macro pastes a field heading and extracts all records for EMPLOY_ID to cell B1 on the macro sheet.

Method 1: To use the Q+E command-equivalent functions with the old Q+E

          Add-in and use the macro in Microsoft Excel 5.0:

          a. To load the old Q+E add-in, choose Add-Ins from
             the Tools menu, and then select the Q+E Integration Add-in
             check box. If you don't see it this option, choose Browse, and
             select the file from the old (version 3.0 or 4.0) EXCEL\QE
             directory.

          b. Run the macro.

          NOTE: For this procedure to work, Q+E must be running or the path
          to the Microsoft Excel directory must be in the PATH statement of
          your AUTOEXEC.BAT file (otherwise only the field headings are
          returned). In Microsoft Excel 5.0c, if Q+E is not running, you
          will get a message stating "Q+E is already running!" and the data
          will be retrieved. Choose OK to dismiss the message.

          add-in:

          a. To change the link from the old Q+E add-in to the new one, do
             the following:

             1) From the Edit menu, choose Links, and select the current
                link to QE.XLA.

             2) Choose the Change Source button to create a link to the new
                EXCEL\LIBRARY\MSQUERY\QE.XLA add-in file, and choose OK
                twice.

          b. To load the Q+E add-in provided with Microsoft Excel 5.0, do
             the following:

             1) Choose the Add-Ins command from the Tools menu and clear
                the Q+E Integration Add-in option, and choose OK. (If you
                don't see the option in the list, skip to step 2).

                This unloads the old Q+E add-in from memory.

             2) From the Tools menu, choose Add-Ins, and select the Q+E
                Add-in option. If you don't see this option, select the
                Browse button and select the QE.XLA file located in the
                new EXCEL\LIBRARY\MSQUERY directory.

          c. Do either of the following to verify that the DB.SET.DATABASE
             function refers to a valid ODBC data source:

              - Create a data source with the name that appears in the
                second argument of the DB.SET.DATABASE function in the ODBC
                Control Panel. For more information about how to add a data
                source, refer to page 149 of the Microsoft Query "User's
                Guide."

                -or-

              - Change all references to your data source (that is,
                dBASEFile, SQLServer, Oracle) to a valid ODBC data source
                name. In the sample macro above, change the second argument
                of the DB.SET.DATABASE statement to a valid data source
                name. Also, change or remove the reference to the data
                source in the SELECT statement in the DB.SQL.QUERY()
                statement to a valid data source name.

REFERENCES

"User's Guide," version 5.0, pages 457-461