Microsoft KB Archive/73306

{| = Excel Macro to Join More Than Two Files in Q+E for Excel =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q73306 The information in this article applies to:
 * 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(&quot;qe&quot;,&quot;system&quot;) A3: =EXECUTE(chan,&quot;[open('c:\excel\qe\emp.dbf','dbasefile')]&quot;) A4: =EXECUTE(chan,&quot;[select.column('DEPT')]&quot;) A5: =EXECUTE(chan,&quot;[open('c:\excel\qe\dept.dbf','dbasefile')]&quot;) A6: =EXECUTE(chan,&quot;[select.column('DEPT_ID')]&quot;) A7: =EXECUTE(chan,&quot;[join]&quot;) A8: =EXECUTE(chan,&quot;[open('c:\excel\qe\addr.dbf','dbasefile')]&quot;) A9: =EXECUTE(chan,&quot;[select.column('LAST_NAME')]&quot;) A10: =EXECUTE(chan,&quot;[activate('query2')]&quot;) A11: =EXECUTE(chan,&quot;[select.column('LAST_NAME')]&quot;) A12: =EXECUTE(chan,&quot;[join]&quot;) A13: =EXECUTE(chan,&quot;[save.query.as(myfile.qef)]&quot;) A14: =TERMINATE(chan) A15: =RETURN

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