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

ID: Q124004

The information in this article applies to:

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:

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.

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.

Method 2

To use a Q+E command-equivalent function with the new 5.0 Q+E add-in:

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.

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.

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."
- 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

Additional query words: 5.00c qpluse QE Integration

Keywords :
Version : 5.00 5.0c
Platform : WINDOWS
Issue type :