Excel Com Addin- paste from clipboard into current open Excel

Question

Hello,

I have created an "Excel 2003 Com Addin". If I click on the commandbarbutton in Excel 2003 a winform will open. This form contains, just keeping it simple a button and a datagrid. I copy h datagridconten into the clipboard with the following code through clikcing on my commandbarbutton.
 

Public Sub copyToClipBoard2()
       Form1.DataGridView2.SelectAll()



        Dim dataObject As DataObject = Form1.DataGridView2.GetClipboardContent
        Clipboard.SetDataObject(dataObject, True)
        Form1.DataGridView2.ClearSelection()



    End Sub

The whole content of my datagridview1 is now copied into the clipboard. The next step should be find out the active cell from Excel and copy the whole content into it. Just a normal copy and paste routine from a datagridview into the active Excel cell.

Can anyone give me some hints how I can copy the content from clipboard into the active Excel cell? (I don't want to write the content from the datagridview into a new Excel workbook.)

Thanks for any help!
Jakob

Answer 1

Any other idea how I could copy the content from the datagrid into the active open Excel cell?

Thanks!

Answer 2

Any other idea how I could copy the content from the datagrid into the active open Excel cell?

Thanks!

It would help us if you could describe what approach (in the link you have in the "deleted" message did not work for you. It would be too bad if we would suggest the same thing that you've already rejected...

I can think of two relatively efficient methods that would not conflict with the user's Clipboard contents:

1. Write the contents of the datagrid to a database-type of file, then use Excel's built-in commands to import that file. This could be, for example, delimited text or XML.

2. Put the contents of the datagrid into an array, the array can then be inserted into the Excel cell and it will write the number of cells required to show the entire contents

If you want to paste to the current cell, then all you should need is the Paste method of the Worksheet object with none of the optional arguments. For example

Application.ActiveSheet.Paste()

According to the object model Help, this will paste the content of the clipboard into the current selection.

---

Cindy Meister, VSTO/Word MVP

Answer 3

Hi YakomozB,

Here is a sample code for pasting the contents of your Clipboard to the active cell. This assumes that your Clipboard contains plain text.


Excel._Application currentApplication;

currentApplication.ActiveCell.Value2 = 
    System.Windows.Forms.Clipboard.GetData(System.Windows.Forms.DataFormats.Text);

Answer 4

@Cindy Meister: The first method I found on http://www.timvw.be/datagridview-to-excel/ there is a good example but this is until now out of my visual vb skills.
The second one looks good for me, but if I paste the code

Application.ActiveSheet.Paste()

It says: Application is no active member of system.windows.forms.application.


@Jose: Mh, your code looks like c#. Can you please post it in VB? Thanks.

Answer 5

@Cindy Meister: The first method I found on http://www.timvw.be/datagridview-to-excel/ there is a good example but this is until now out of my visual vb skills.
The second one looks good for me, but if I paste the code

Application.ActiveSheet.Paste()

It says: Application is no active member of system.windows.forms.application.


@Jose: Mh, your code looks like c#. Can you please post it in VB? Thanks.

When discussing an Office object model the "Application" property refers to the Office application, not a Windows Forms or any other kind of .NET application. You need to specify (in this case) the Excel application as the namespace. Since you're displaying a Windows Form and calling back into the Excel application from that you should pass the Excel application object (Globals.ThisAddin.Application should work) to the constructor of the Windows Form class. Also declare a variable for the Excel.Application at the class level. Assign the object you pass in to the constructor to that class level member, then you can use that in your Windows Form code.

---

Cindy Meister, VSTO/Word MVP

Answer 6

@Jose: Mh, your code looks like c#. Can you please post it in VB? Thanks.

Dim currentApplication As Microsoft.Office.Interop.Excel._Application = Globals.ThisAddin.Application

currentApplication.ActiveCell.Value2 = System.Windows.Forms.Clipboard.GetData(System.Windows.Forms.DataFormats.Text)

 

Answer 7

Thanks both of you. Learned a lot. Each day is a day for new ideas, questions and solutions.

@Jose: Your code works very well. Can you please tell me how I could keep the rows and columns. For instance, if I copy a 2rows and 2 columns from the datagridview into my clipboard and I want to paste them starting from the active Excel cell. How can I do this? ( now the whole result is paste just in one cell)

Answer 8

In my earlier reply to your message I gave you this information

<<If you want to paste to the current cell, then all you should need is the Paste method of the Worksheet object with none of the optional arguments. For example

Application.ActiveSheet.Paste()>>

José is not pasting from the Clipboard, he's just accessing the contents as text and inserting that into the target cell.

---

Cindy Meister, VSTO/Word MVP

Answer 9

Merci. Thanks for your fast answer.