How to Concatenate DDE to MSQuery Statements (149248)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. For example, consider the following line of DDE to Microsoft Query code:

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

In this example, note the following:

• 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.

When you "break" a DDE to Microsoft Query statement for Concatenation, it is critical to preserve any apostrophes that are present. The following are two methods you can use to do this:

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)

The variable "FetchLoc" can now be concatenated in the DDE to Microsoft Query statement, as in the following example:

NOTE: Remember to retain the apostrophes.

   DDEExecute Chan, ("[Fetch('Excel','Sheet1','" & _

       FetchLoc & "','All/Headers')]")