Using Tabs and Paste.Append from an Excel Macro to Q+E

SUMMARY

You can use the Paste.Append function of Q+E inside an Excel Execute function to append data to your database in Q+E. You can do this without copying anything to the Clipboard by using a string of field values separated by tabs.

MORE INFORMATION

The Paste.Append function of Q+E has one optional argument, "field_values". If "field_values" is omitted, the new values are pasted from the Clipboard. "field_values" is a single argument with tabs separating field values. To use the argument "field_values", you must use the Excel Char(9) function to concatenate tab characters. The number nine is the ASCII code for the tab character.

The example below does the following:

1. Opens a DDE channel from Excel to Q+E.

2. Opens a query window in Q+E for the file EMP.DBF.

3. Turns on Allow Editing.

4. Appends the data in cell A7, A8, and A9 on the Excel macro sheet into adjacent fields at the bottom of EMP.DBF.

5. Terminates the channel.

Example

A1: chan=INITIATE("QE","System") A2: =EXECUTE(chan,"[open('emp.dbf','dBasefile')]") A3: =EXECUTE(chan,"[Allow.Edit(true)]") A4: =EXECUTE(chan,"[Paste.Append('"&A7&CHAR(9)&A8&CHAR(9)&A9&"')]") A5: =TERMINATE(chan) A6: =RETURN() A7: John A8: Smith A9: 12345

Notes

You do have to include closing parenthesis for the Execute function; the book leaves these out.

After the first open parenthesis in the Paste.Append function, there is a single quotation and then a double quotation mark.

At the end of the Paste.Append function, before its close parenthesis, there is a double quotation and then a single quotation mark.

Additional Example

There is an example on page 105 of the "Q+E for Microsoft Excel User's Guide" that describes using Paste.Append from an Excel macro, but it does not describe how to concatenate cell contents into the "field_values" argument.

REFERENCES

"Q+E for Microsoft Excel User's Guide." Version 3.00, page 105.