How to handle objects drag-drop? (Excel 2007 + VS 2008)

[Tomasz Jastrzebski]

Hi,

I have Excel 2007 add-in VS 2008 application.

I created Custom Task Pane containing TreeList and would like to drag
TreeList elements and drop them into Excel cell which action would change
cell value.

I guess I neet to handle some Workbook or Application event but cannot find
anything useful.
How do I go about this?

Thanks,

Thomas

 

[Jie Wang [MSFT]]

Hi Thomas,

When you say TreeList do you mean the TreeView Control?

If that was the case, you don't need to do any work on the Excel side, just
handle the TreeView's ItemDrag event, in the event handler, call the
control's DoDragDrop method to start a drag & drop operation, once the item
is being dropped onto a cell, Excel will do its job to get the data you set
in the DoDragDrop method.

Sample code (suppose the TreeView control's name is tvData):

VB.NET

Private Sub tvData_ItemDrag(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.ItemDragEventArgs) Handles tvData.ItemDrag
Dim item As TreeNode = TryCast(e.Item, TreeNode)

If (item IsNot Nothing) Then
tvData.DoDragDrop(item.Text, DragDropEffects.Copy)
End If
End Sub

C#

private void tvData_ItemDrag(System.Object sender, ItemDragEventArgs e)
{
TreeNode item = e.Item as TreeNode;

if (item != null)
{
tvData.DoDragDrop(item.Text, DragDropEffects.Copy);
}
}

You can also customize the data before sending it to the DoDragDrop method.

If you have any further questions regarding this issue, please feel free to
post here.

Regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tomasz Jastrzebski]

Thanks!
It worked.
TJ

 

[Tomasz Jastrzebski]

One more question.

How do I modify code like below

tvData.DoDragDrop("=MyFn(" + item.Text +")", DragDropEffects.Copy)

so my custom function gets pasted and evaluated, rather than just a text?
The problem is not the function (when typed into cell it works) but that
Excel does not evaluate text copied this way as a function.

Tomasz

 

[Jie Wang [MSFT]]

Hi Tomasz,

The following code works on my side:

tvData.DoDragDrop(String.Format("=SayHello(""{0}"")", item.Text),
DragDropEffects.Copy)

Where SayHello function is defined in VBA.

Drag & drop built-in functions also works:

tvData.DoDragDrop("=TODAY()", DragDropEffects.Copy)

Could you verify the actual string you're sending to drag & drop contains
the correct format of the formular?

Thanks,

Jie

 

[Jie Wang [MSFT]]

Hi Tomasz,

Any updates on this issue?

If there were anything I missed in the formular drag-drop issue, please let
me know and I'll see how to fix it.

Thanks,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Pedro]

Hello Jie Wang,

I'm also trying to handle drag-drop into Excel 2007.
I done the DoDragDrop method and it worked, but i need other thing.

I would like to display a context menu when i drop the value, so it can do
some kind of operation. Is there any event i can handle to do it?

For example:
I'm dragin some values, and when i drop them, i want the following options:
- Convert to Euro
- Convert to Dollar
- ... (Other operations)

Can this be done? (i notice that if i click the right button while dragging
it appear a context menu)

Hi Thomas,

When you say TreeList do you mean the TreeView Control?

If that was the case, you don't need to do any work on the Excel side, just
handle the TreeView's ItemDrag event, in the event handler, call the
control's DoDragDrop method to start a drag & drop operation, once the item
is being dropped onto a cell, Excel will do its job to get the data you set
in the DoDragDrop method.

Sample code (suppose the TreeView control's name is tvData):

VB.NET

Private Sub tvData_ItemDrag(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.ItemDragEventArgs) Handles tvData.ItemDrag
Dim item As TreeNode = TryCast(e.Item, TreeNode)

If (item IsNot Nothing) Then
tvData.DoDragDrop(item.Text, DragDropEffects.Copy)
End If
End Sub

C#

private void tvData_ItemDrag(System.Object sender, ItemDragEventArgs e)
{
TreeNode item = e.Item as TreeNode;

if (item != null)
{
tvData.DoDragDrop(item.Text, DragDropEffects.Copy);
}
}

You can also customize the data before sending it to the DoDragDrop method.

If you have any further questions regarding this issue, please feel free to
post here.

Regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.