none
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
    • Edited by YakomozB Saturday, March 28, 2009 7:52 PM Specification of the problem
    Friday, March 27, 2009 4:32 PM

Answers

  • 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
    • Marked as answer by YakomozB Tuesday, March 31, 2009 3:00 PM
    Tuesday, March 31, 2009 2:55 PM
    Moderator

All replies

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

    Thanks!
    Monday, March 30, 2009 4:43 PM
  • 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
    Monday, March 30, 2009 4:56 PM
    Moderator
  • 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);

    Monday, March 30, 2009 4:57 PM
  • @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.



    Monday, March 30, 2009 5:34 PM
  • @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
    Monday, March 30, 2009 6:05 PM
    Moderator
  • @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)

     

    Tuesday, March 31, 2009 2:48 AM
  • 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)
    Tuesday, March 31, 2009 2:45 PM
  • 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
    • Marked as answer by YakomozB Tuesday, March 31, 2009 3:00 PM
    Tuesday, March 31, 2009 2:55 PM
    Moderator
  • Merci. Thanks for your fast answer.
    • Marked as answer by YakomozB Tuesday, March 31, 2009 3:00 PM
    • Unmarked as answer by YakomozB Tuesday, March 31, 2009 3:00 PM
    Tuesday, March 31, 2009 3:00 PM