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

ID: Q82157

The information in this article applies to:

Microsoft Visual Basic Standard and Professional Editions for Windows, versions 2.0, 3.0
Microsoft Visual Basic programming system for Windows, version 1.0

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 "Steps to Reproduce Behavior" section below, you can work around the undesired behavior by placing the following value in the ListTopic property:


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

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:


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

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 "hey" + Chr$(9) + "is"
      List1.AddItem "for" + Chr$(9) + "horses"
   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("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:

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 = "R" + Row$ + "C1"

to:


   DDEbox.LinkItem = "R"+ 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 :
Version :
Platform :
Issue type :