Excel: Invalid Field Name Accessing External Excel Database

SUMMARY

Accessing an external Microsoft Excel database using the QE.XLA add-in macro may result in the error "Invalid Field Name" if the field names in the database are not typed in all uppercase characters.

MORE INFORMATION

The add-in macro QE.XLA provides easy access to external databases from an Excel worksheet; however, because Q+E is providing the access, the field names must be in dBASE format or the "Invalid Field Name" error will result. Either the field names must be in all uppercase, or the field names used in the criteria and extract ranges must have quotation marks, using the left apostrophe character.

Workaround

The following workaround assumes you have QE.XLA loaded.

To get the external database to work properly, you must either enter the field names in all uppercase or enter the field names in your Criteria and Extract ranges using the Paste Fieldnames command from the Data menu.

Note that lowercase and mixed-case names are entered with quotation marks, using the left apostrophe. For example, the following is entered when using the Paste Fieldnames command:

   `Name` when using Paste Fieldnames.

Steps to Reproduce the Problem

1. Create a database in Excel using field names in lower or mixed case and copy these field names to a second worksheet in two separate areas for setting up criteria and extract ranges later.

2. Save and close the file containing the database.

3. Select the field names and the cell below them in your criteria range and choose Set Criteria from the Data menu.

4. Select only the field names in your Extract range and choose Set Extract from the Data menu.

5. Open QE.XLA from the QEMACRO subdirectory in the XLSTART subdirectory.

6. From the Data menu, choose Set Database.

7. In the dialog box that appears, select External Database and then choose OK.

8. In the resulting dialog box, select ExcelFile from the Source list box and select your database from the Files list box. Then choose OK. NOTE: You may have to change to the directory containing your file if it doesn't show in the File list box.

9. Choose OK again to confirm your selection.

10. Enter a value in your criteria range and perform an extract. The Extract dialog box is displayed and the Unique Records Only option is not available (dimmed). Choose OK. Excel will display an alert indicating an invalid field name.

REFERENCES

"Q+E for Microsoft Excel User's Guide," version 3.0, pages 57-66