XL: How to Concatenate DDE to MSQuery Statements

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Query for Windows, version 1.0
• Microsoft Query for Windows 95, version 2.0
• Microsoft Query for Windows, version 8.0
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition
• Microsoft Query for the Macintosh, version 1.0
• Microsoft Query for the Macintosh, version 8.0

SUMMARY

In Microsoft Query, to use concatenated variables, and to know where and how to break a line of code for Line Continuation, you need to know how dynamic data exchange (DDE) to Microsoft Query statements are formatted. This article provides an understanding of the rules governing manipulation of the DDE to Microsoft Query statements.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   DDEExecute Chan, "[UserControl('&Return to Excel',3)]"

• Everything within the outer quotation marks is a Visual Basic for Applications text string to be passed to the DDE Channel.
• Everything within the square brackets is passed across the DDE channel to Microsoft Query.
• Everything within the parentheses is a Microsoft Query function argument and must match the Query function's requirements.
• Everything within the apostrophes is a string to Microsoft Query.

Concatenation Method 1: Leave the Apostrophes Within the Quotation Marks

1. Before you can break a line inside a Visual Basic for Applications text string (within the outer quotation marks), you must "wrap" each section of the text string in quotation marks, removing the text that will be replaced by the concatenated variable.

   In the following example, the apostrophes are preserved within the string text:

         DDEExecute Chan, "[UserControl('" <break> "',3)]"
   

2. Load the variable with the data to be concatenated, as illustrated below:

         X = "&Return to Excel"
   

3. At the point in the string where you need to insert the concatenation variable, use a concatenation character sequence (<space>&<space>) on both sides of the inserted variable, as in the following example:

         DDEExecute Chan, "[UserControl('" & X & "',3)]"
   

Concatenation Method 2: Preserve the Apostrophes Within the Variable

To do this, perform the following steps:

1. Before you can break a line inside a Visual Basic for Applications text string (within the outer quotation marks), you must "wrap" each section of the text string in quotation marks, and remove the text that will be replaced by the concatenated variable.

   If this option is used, the example would look like the following:

         DDEExecute Chan, "[UserControl(" <break> ",3)]"
   

2. Load the variable with the data to be concatenated. Note that in this case the apostrophes are included in the variable. See the following example:

         X = "'&Return to Excel'"
   

3. At the point in the string where you need to insert the concatenation variable, use a concatenation character sequence (<space>&<space>) on both sides of the inserted variable. Note that the apostrophes are NOT included in the statement, as illustrated below:

         DDEExecute Chan, "[UserControl(" & X & ",3)]"
   

Line Continuation Example

When you "break" a DDE to Microsoft Query statement for Line Continuation, use the following steps:

1. Before you can break a line inside a Visual Basic for Applications text string (within the outer quotation marks), you must "wrap" both sections of the text string in quotation marks. See the following example:

         DDEExecute Chan, "[UserControl('&Re" <break> "turn to Excel',3)]"
   

2. At the point in the string where the break is to occur, use a concatenation character sequence (<space>&<space>) between each text string, as in this example:

         DDEExecute Chan, "[UserControl('&Re" & "turn to Excel',3)]"
   

3. Add the line continuation character <_>, as in the following example:

         DDEExecute Chan, "[UserControl('&Re" & _
             "turn to Excel',3)]"
   
   

Statement Formatting Example

When you concatenate variables, the format of the data used must match that which is required by Microsoft Query.

For example, when you return data to Microsoft Excel from Microsoft Query using DDE, you must address the worksheet range in which you want to place the requested data in the R1C1 reference style.

This example shows how to format the variable "FetchLoc" in the R1C1 reference style, as required by Microsoft Query:

   FetchLoc = fetchdata.Address(referenceStyle:=xlR1C1) _
       & ":" & fetchdata.Offset(NumRows(1), _
       NumCols(1)).Address(referenceStyle:=xlR1C1)

NOTE: Remember to retain the apostrophes.

   DDEExecute Chan, ("[Fetch('Excel','Sheet1','" & _
       FetchLoc & "','All/Headers')]")