ID: Q124004
5.00 5.0c WINDOWS kbprg kbmacro
The information in this article applies to:
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.
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)
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.
=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()
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.
To use a Q+E command-equivalent function with the new 5.0 Q+E 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.
"User's Guide," version 5.0, pages 457-461
KBCategory: kbprg kbmacro KBSubcategory:
Additional reference words: 5.00 5.00c qpluse QE Integration
Version : 5.00 5.0c
Platform : WINDOWSLast Reviewed: May 21, 1998