PRB: Strings w/ TABs Sent from VB to Excel via DDE Are Chopped (82157)

   DDEbox.LinkTopic = "R" + Row$ + "C1:R" + Row$ + "C2"
				

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:

   "The cow jumped" + Chr$(9) + "over the moon"
				

the only thing you will see on the Excel side is "The cow jumped." 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.

1. Start a new project in Visual Basic (ALT, F, N). Form1 is created by default.
2. Put a text box (Text1) on Form1, and change its name from Text1 to DDEbox.
3. Put a list box (List1) and a command button (Command1) on Form1.
4. Add the following code to the Form_Load procedure:

      Sub Form_Load ()
         Form1.Show
         ' Add items to list box with TABs embedded.
         List1.AddItem "hey" + Chr$(9) + "is"
         List1.AddItem "for" + Chr$(9) + "horses"
      End Sub
   						

5. 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("Excel", 4)   ' Start Excel.
            ' Set link topic.
            DDEbox.LinkTopic = "Excel|Sheet1"
            DDEbox.LinkItem = ""     ' 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 = "R"+Row$   ' Take out comment to send entire
                                           ' string.
            ' Comment next line when uncommenting above line.
            DDEbox.LinkItem = "R" + Row$ + "C1"   ' 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
   						

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:

If you change the assignment statement of the LinkItem of the DDEbox from:

   DDEbox.LinkItem = "R" + Row$ + "C1"
				

to:

   DDEbox.LinkItem = "R"+ Row$
				

the entire string is passed to Excel with the following results: