Microsoft KB Archive/82157

= PRB: Strings w/ TABs Sent from VB to Excel via DDE Are Chopped =

Article ID: 82157

Article Last Modified on 12/12/2003

-

APPLIES TO


 * Microsoft Visual Basic 2.0 Standard Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 2.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 1.0 Standard Edition

-



This article was previously published under Q82157



SYMPTOMS
Unexpected behavior may occur in a dynamic data exchange (DDE) conversation from Visual Basic to Microsoft Excel when you send a string that contains TAB characters.

If you specified a specific row and column in the Visual Basic LinkItem property, the string may be truncated in Excel. If you didn't specify a column in the LinkItem property but only specified a specific row, the string will be parsed by Excel, and each TAB will cause the characters following the TAB to be entered into the following cell in Excel.



CAUSE
The reason for this behavior is that Excel uses TABs as its delimiter. You can use this method to send multiple items to Excel, placing them in their own cells if desired.



RESOLUTION
Either don't send strings that contain TABs or else provide more complete information in the LinkTopic property. For example, in the code shown in the &quot;Steps to Reproduce Behavior&quot; section below, you can work around the undesired behavior by placing the following value in the ListTopic property: DDEbox.LinkTopic = &quot;R&quot; + Row$ + &quot;C1:R&quot; + Row$ + &quot;C2&quot; By specifying a larger selection of cells, you can ensure that the data being passed will not be truncated and that the embedded TAB, RETURN, or LINEFEED characters will be interpreted correctly as the next column or next row.



STATUS
This behavior is by design.



MORE INFORMATION
This behavior occurs when the following is true:


 * A string that you are trying to send to Excel through DDE contains an embedded TAB.
 * You set your LinkItem property to a specific Excel cell (both row and column, such as R1C1, meaning row 1 column 1).

The attempted conversation will result in a truncated string. For example, if both conditions are true and you pass the following string to Excel: &quot;The cow jumped&quot; + Chr$(9) + &quot;over the moon&quot; the only thing you will see on the Excel side is &quot;The cow jumped.&quot; The rest of the string will be lost.

Steps to Reproduce Behavior
The following example passes strings to Excel from a list box that has TAB- delimited columns. Run the program twice, and change the LinkItem line from a comment into an executed line of code, and observe the different behavior.

 Start a new project in Visual Basic (ALT, F, N). Form1 is created by default. Put a text box (Text1) on Form1, and change its name from Text1 to DDEbox. Put a list box (List1) and a command button (Command1) on Form1.  Add the following code to the Form_Load procedure: Sub Form_Load Form1.Show ' Add items to list box with TABs embedded. List1.AddItem &quot;hey&quot; + Chr$(9) + &quot;is&quot; List1.AddItem &quot;for&quot; + Chr$(9) + &quot;horses&quot; End Sub   Add the following code to the Command1_Click event procedure: Sub Command1_Click Const NONE = 0, COLD = 2    ' Define constants.

If DDEbox.LinkMode = NONE Then Z% = Shell(&quot;Excel&quot;, 4)  ' Start Excel. ' Set link topic. DDEbox.LinkTopic = &quot;Excel|Sheet1&quot; DDEbox.LinkItem = &quot;&quot;    ' Set link item. DDEbox.LinkMode = COLD  ' Set link mode. End If

' Loop through all items in list box: For i% = 0 To List1.ListCount - 1 Row$ = Format$(i% + 1)        ' Format row variable. ' DDEbox.LinkItem = &quot;R&quot;+Row$  ' Take out comment to send entire ' string. ' Comment next line when uncommenting above line. DDEbox.LinkItem = &quot;R&quot; + Row$ + &quot;C1&quot;  ' This statement truncates ' string in Excel. DDEbox.text = List1.list(i%) ' Assign text box to list box string. DDEbox.LinkPoke    ' Send the string to Excel. Next

DDEbox.LinkMode = NONE End Sub </li></ol>

For best results, make sure Excel is not running before you start the program. When you start the program, notice the list box has the strings added to it during the form Load event. If you choose the command button to initialize the DDE conversation with the program typed in exactly as shown, the following will appear in Excel:

hey ' This will be in cell A1.

for ' This will be in cell A2.

If you change the assignment statement of the LinkItem of the DDEbox from: DDEbox.LinkItem = &quot;R&quot; + Row$ + &quot;C1&quot; to: DDEbox.LinkItem = &quot;R&quot;+ Row$ the entire string is passed to Excel with the following results:

hey is ' These words will be in A1 and B1.

for horses ' These words will be in A2 and B2.

Additional query words: 2.00 3.00

Keywords: kbprb KB82157

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.