ID Number: Q82157
1.00
WINDOWS
Summary:
If you send strings containing TAB characters in a dynamic data
exchange (DDE) conversation from Microsoft Visual Basic to Microsoft
Excel, the string may be truncated in Excel if you specify a specific
row and column in the Visual Basic LinkItem property. If you do not
specify a column in the LinkItem property but only specify a specific
row, your 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.
This information applies to Microsoft Visual Basic programming system
version 1.0 for Windows.
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 you pass the following string to Excel
"The cow jumped" + Chr$(9) + "over the moon"
and if the two conditions above are true, the only thing you will see
on the Excel side is "The cow jumped". The rest of the string will be
lost.
The following code example passes strings to Excel from a list box
with TAB-delimited columns. Run the program twice, and uncomment the
LinkItem line to see the different behavior.
Steps to Reproduce Behavior
---------------------------
1. Run Visual Basic, or from the File menu, choose New Project (ALT,
F, N) if Visual Basic is already running. Form1 is created by
default.
2. Put a text box on the form (Form1), and change the CtlName property
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 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$
notice that 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
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. If that is not the desired result, you
will have to make sure you compensate for the lost parts of the string.
Additional reference words: 1.00