(To view best on-screen in Windows Notepad, maximize the Notepad
window and turn on wordwrap if it is not already on -- from the
Edit menu, choose Word Wrap. For best printed results, open this
document in Windows Write, Microsoft Word, or another word processor,
select the entire document and format the text in 10 point Courier
before printing.)
--------------------------------------
Notes on using Q+E for Microsoft Excel
--------------------------------------
Contents
Outdated Driver Files
SQL Server Compatibility ** IMPORTANT: New Information **
QE.EXE
dBASE IV Compatibility
Field Names from Microsoft Excel Documents
QE.XLA
Command Equivalent Functions
SQL Query Command
Text Files
"Remote Links Exist" message
Using Q+E with DEC Rdb
Errata: Q+E for Microsoft Excel User's Guide
---------------------
Outdated Driver Files
---------------------
If you previously installed a version of Q+E earlier than version 3.0a, the following driver files will have been copied to your Windows \SYSTEM directory: QEDBF.DLL, QESS.DLL, QEXLS.DLL, QETXT.DLL and QEXLA.DLL. Q+E versions 3.0a and later install new versions of these files in the directory containing EXCEL.EXE. When you install Q+E with Excel, Setup adds the Excel directory to the Path command in your AUTOEXEC.BAT file so that, after restarting your computer, Q+E will find the new drivers instead of the older versions. If you install Q+E separately from Excel, or choose not have Setup modify your AUTOEXEC.BAT file, Q+E will not find the new drivers and you will receive the following message:
The DLL for this source is out of date. Reinstall the driver
or remove the directory containing the old DLL from your path.
The name of the driver will also be displayed in the error message.
If this occurs, do the following:
1. Remove the following files from your Windows \SYSTEM directory:
QEDBF.DLL QESS.DLL QEXLS.DLL QETXT.DLL
2. Add the directory containing Q+E to the Path command in
your AUTOEXEC.BAT file.
------------------------
SQL Server Compatibility
------------------------
IMPORTANT: New Information for Drivers Installed with Microsoft Excel Version 4.0a.
When you install Q+E with Microsoft Excel version 4.0a, versions of DBNMP3.DLL and W3DBLIB.DLL are installed in your Microsoft Excel directory. If you use or plan to use SQL Server in conjunction with Q+E, the following information affects you, depending on which SQL Server version you use and how you use SQL Server.
^ If you use SQL Server version 1.1:
You need to take no special action; use the default installation of Q+E.
^ If you use SQL Server version 4.2 in conjunction
with Q+E only; i.e. you don't need SQL Server version
4.2 in conjunction with any other applications:
For Q+E and Microsoft Excel version 4.0a, you must remove DBNMP3.DLL and W3DBLIB.DLL from your Windows \SYSTEM directory, or you may rename these files (e.g. with the extension .BAK) so that Q+E uses the correct versions of these files installed in your Microsoft Excel directory when you install Q+E. You must also ensure that your Microsoft Excel directory is listed in the Path statement in your AUTOEXEC.BAT file.
^ If you use SQL Server version 4.2, and you use it
in conjunction with other Windows database applications
that utilize SQL 4.2 in addition to Q+E:
In this case, the other application(s) also use DBNMP3.DLL and/or W3DBLIB.DLL. You need to place the version of these .DLL files that the application(s) require in each application's directory, and remove them from your Windows \SYSTEM directory, or rename them (e.g. with the extension .BAK). This will ensure that the correct .DLL files are used by the application, and they will not conflict with the versions of these files used by Q+E. Ensure that each application's directory is listed in the Path statement in your AUTOEXEC.BAT file.
The version of Q+E that ships with Microsoft Excel version 4.0a doesn't support all of the new data types supported by SQL 4.2. In particular, if you attempt to open a table containing 'real' or 'smalldata' data types when using the SQL 4.2 versions of DBNMP3.DLL and W3DBLIB.DLL (not the versions that ship with Excel) you may hang or crash. This is the primary reason for using the versions of DBNMP3.DLL and W3DBLIB.DLL that ship with Excel rather than the version supplied with your SQL 4.2 installation.
------
QE.EXE
------
dBASE IV Compatibility
----------------------
By default, Q+E operates on dBASE files according to dBASE IV compatibility. For most uses, ANSI compatibility provides more predictable results in terms of handling the sorting, averaging, counting, and displaying of Null values. Also, using UPPER and LOWER functions according to dBASE IV compatibility does not change the case of international characters. To change your compatibility default to ANSI,
select Options in the dBASEFile Open dialog, select ANSI as the compatibility, and choose OK with the Set Default checkbox selected. For a more thorough explanation of the differences between dBASE IV SQL and ANSI SQL, refer to Appendix A, "Using Q+E with dBASE", in your "Q+E for Microsoft Excel User's Guide."
Changing or adding records in the Q+E Data Form dialog will not update or insert new records until you move to the next record or exit Data Form.
If you will be exchanging text files between Q+E and Microsoft Excel, be sure to save them with a tab separator and a .TXT extension. If you are saving the file as a .CSV file, be sure to use the separator indicated in your Microsoft Windows Control Panel.
Field Names from Microsoft Excel Documents
-----------------------------------------
Q+E encloses field names from Microsoft Excel documents that contain spaces or lowercase letters with the ` character. To avoid possible conflicts with extracting data to Microsoft Excel, or specifying criteria, use only uppercase characters in your ExcelFile database tables, and use underscore characters instead of spaces.
Note: It is recommended that you do not execute database functions while
---- in workgroup mode.
------
QE.XLA
------
Command Equivalent Functions
----------------------------
Many of the command equivalent functions in QE.XLA turn off error checking at run time. If you use QE.XLA command equivalents and error checking in a macro, insert ERROR(TRUE) following the QE.XLA command equivalent to turn error checking back on.
SQL Query Command
-----------------
QE.XLA provides a SQL Query command for sending SQL statements directly to Q+E. When using this command, include the source name in the table specification of the FROM line. This tells Q+E which source to use in opening the file.
For example, "SELECT * FROM dBASEFILE|C:\emp.dbf" selects all the records from the dBASE file named "emp.dbf", located in the root directory.
If you will be saving queries from the SQL Query dialog to be opened by Q+E, include the Q+E headerline "Q+E 3.0 for <source name>" as the first line in the statement, where <source name> is the name of the source for Q+E to use in executing the specified query. For example, "Q+E 3.0 for ExcelFile" specifies that this .QEF file will be used by Q+E to open an ExcelFile database table. **NOTE: the format 'source|tablename' must not be used here. Simply use 'tablename' alone (i.e. 'C:\EXCEL\ADDR.DBF').
SQL Query allows you to run just a portion of the text in the SQL Query dialog by selecting that text prior to choosing Run. If no text is selected, SQL Query sends the entire statement to Q+E.
Text files
----------
By default, all fields in a textfile database table will be read as character fields. To extract formatted data, or do extracts on data such as dates or numeric values, select the "Guess Data Types" option in the Q+E Open Textfile Options dialog and select the Set Default checkbox. Fixed Length Files will always be read by QE.XLA as a single column of data. For best results, open the Fixed Length file first in Q+E or Microsoft Excel, parse the information into columns, and save it as a character-delimited file.
"Remote Links Exist" message
----------------------------
Closing a file after extracting information from an external database displays the message
Remote links to document exist. Close anyway?
This message is advising you that a connection has been made between the document and Q+E to exchange information. Choose OK to proceed with closing the file.
QE.XLA Helpful Hints
--------------------
SQL QUERY Command (Source Name): QE.XLA provides a SQL Query command for sending SQL statements directly to Q+E. Be sure to include the source name in the table specification of the FROM line to tell Q+E which source to use in opening the file.
For example, "SELECT * FROM dBASEFile|C:\emp.dbf" selects all the records from the dBASE file named "emp.dbf", located in your boot directory.
SQL QUERY Command (Saving a Query): If you will be saving queries from the SQL Query dialog to be opened by Q+E, be sure to include the Q+E headerline "Q+E 3.0 for <source name>" as the first line in the statement, where <source name> is the name of the source for Q+E to use in executing the specified query. For example, "Q+E 3.0 for ExcelFile" specifies that this .QEF file will be used by Q+E to open an ExcelFile database table. **NOTE: the format 'source|tablename' must not be used here. Simply use 'tablename' alone (i.e. 'C:\EXCEL\ADDR.DBF').
----------------------
Using Q+E with DEC Rdb
----------------------
In the Schema Statement box, enter the necessary Schema Statement to connect. See your database administrator, or DEC Rdb manuals for details.
-------------------------------------------
Errata: Q+E for Microsoft Excel User's Guide
-------------------------------------------
Chapter: 1, Q+E Basics
Section: Installing Q+E
Page 68:
In the fourth paragraph, the statement that Setup modifies
your WIN.INI file is incorrect. Setup does not modify your
WIN.INI file; instead, it creates a QE.INI file in your
Windows directory. QE.INI contains Q+E driver information.
Chapter: 8, Transferring Data to Other Applications
Section: Linking Another Application to Q+E
Page 49:
The statement, "However, the data remains linked to the
underlying database through Q+E," is incorrect. The data is
not linked to the underlying database, but to Q+E itself.
Chapter: 9, Using Micrsoft Excel to Access Data on External Databases
Section: Using Macro Functions with Q+E
Function: DB.DELETE
Page 68:
The heading shows only the question form of the function.
There is a standard form as well. Also, change DB.DELETE?()
to DB.DELETE() -- no question mark in the second-to-last line
in the paragraph below the heading.
Function: DB.PASTE.FIELDNAMES
Page 71:
The second argument should read "paste_fields", not
"fieldname". Also, when using this function, use one or the
other argument (or neither), but not both.
Chapter: 11, Q+E Execute Command Reference
Command: PASTE()
Page 105:
The example at the top of the page is missing a closing
parenthesis at the end of each of the four lines. Please
add these.
Command: SAVE.LABEL.AS()
Page 109:
To create a label with spaces between each item, use two sets
of double quote marks around a blank space. For example:
