Excel Macro to Join More Than Two Files in Q+E for Excel

• Microsoft Excel for Windows, version 3.0
• Microsoft Excel for OS/2, version 3.0

SUMMARY

The JOIN macro command is equivalent to the JOIN command from the Select menu in a Query window. It is possible to use the JOIN macro to join more than two files in Q+E, but in sets of two at a time only.

MORE INFORMATION

The following macro uses example files located in the QE subdirectory of your EXCEL directory.

The macro opens EMP.DBF, selects the column DEPT, opens DEPT.DBF, selects the column DEPT_ID, and then performs the join. The macro then opens a third dBASE file, ADDR.DBF, selects the matching column in that database, and performs another join resulting in a query containing information from all three databases.

Particular attention should be given to the punctuation of the macro, especially the path structure to the files opened and the capitalization to the field headings. A #REF! error will result if the macro cannot find the files and/or recognize the field names displayed in the query window.

A1: =WORKSPACE(,,,,,,,FALSE) A2: chan=INITIATE("qe","system") A3: =EXECUTE(chan,"[open('c:\excel\qe\emp.dbf','dbasefile')]") A4: =EXECUTE(chan,"[select.column('DEPT')]") A5: =EXECUTE(chan,"[open('c:\excel\qe\dept.dbf','dbasefile')]") A6: =EXECUTE(chan,"[select.column('DEPT_ID')]") A7: =EXECUTE(chan,"[join()]") A8: =EXECUTE(chan,"[open('c:\excel\qe\addr.dbf','dbasefile')]") A9: =EXECUTE(chan,"[select.column('LAST_NAME')]") A10: =EXECUTE(chan,"[activate('query2')]") A11: =EXECUTE(chan,"[select.column('LAST_NAME')]") A12: =EXECUTE(chan,"[join()]") A13: =EXECUTE(chan,"[save.query.as(myfile.qef)]") A14: =TERMINATE(chan) A15: =RETURN()

NOTE: The order in which the files are selected affects the outcome of the join.

REFERENCES

"Q+E For Microsoft Excel User's Guide," version 3.0, chapters 10 and 11