Exception Visual Basic NET Clipboard Text to Excel Via Interop Worksheet Paste

0 votes

Trying to get an older VB.NET application working again. One feature builds a text string composed of text delimited by Tab/Return characters, then creates (via interop) an Excel Workbook, adds a Worksheet, and (desired) paste the text string into the worksheet.

Here is the code:

Private Function AddNewWorksheetToWorkbook(

ByVal theWorkbook As Workbook,
ByVal worksheetName As String,
ByVal textToPaste As String

) As Microsoft.Office.Interop.Excel.Worksheet

        Dim newWorksheet As Microsoft.Office.Interop.Excel.Worksheet

        newWorksheet = theWorkbook.Worksheets.Add()
        newWorksheet.Name = worksheetName
        theWorkbook.Save()
        newWorksheet.Activate()                            'All works fine, file saved, worksheet named and Active as desired

        Dim app As Microsoft.Office.Interop.Excel.Application
        app = newWorksheet.Application

        If app.ActiveSheet.Name = newWorksheet.Name Then   'Just a test to make sure ActiveSheet is the one desired -- it is
            Clipboard.SetText(textToPaste)                 'Clipboard has text delimited by vbTab and vbReturn (a "plain" text table)
            newWorksheet.Range("A1").Select()              'Cell "A1" is properly selected
            newWorksheet.Paste()                           'BOOM! Get System.Runtime.InteropServices.COMException: 'Microsoft Excel cannot paste the data.'
        End If

        theWorkbook.Save()

        Return newWorksheet

End Function

As noted in the comments, all goes well until the Worksheet.Paste() method call.

I have tried variations on Paste() as well as PasteSpecial(), etc. No joy.

Keep getting System.Runtime.InteropServices.COMException: 'Microsoft Excel cannot paste the data.'

I am able to (manually, not through interop) click "Paste" in Excel and it works just fine.

I would be grateful for any insights from the stackoverflow community!

Mar 25, 2022 in Database by Edureka
• 13,690 points
1,086 views

1 answer to this question.

0 votes

So, here's what I did to address (or, more accurately, avoid and solve) the issue I was having. This is how I changed the function that was already in place.

Private Function AddNewWorksheetToWorkbook(

ByVal theWorkbook As Workbook,
ByVal worksheetName As String,
ByVal textToPaste As String

) As Microsoft.Office.Interop.Excel.Worksheet

        Dim newWorksheet As Microsoft.Office.Interop.Excel.Worksheet

        newWorksheet = theWorkbook.Worksheets.Add()
        newWorksheet.Name = worksheetName
        theWorkbook.Save()
        newWorksheet.Activate()                            'All works fine, file saved, worksheet named and Active as desired

        Dim app As Microsoft.Office.Interop.Excel.Application
        app = newWorksheet.Application

        If app.ActiveSheet.Name = newWorksheet.Name Then   

            Dim rowCount As Integer = 0
            Dim colCount As Integer = 0
            Dim values(,) As String = ExtractTwoDimDataSet(pasteText, rowCount, colCount)

            Dim oRange As Range
            oRange = newWorksheet.Range(newWorksheet.Cells(1, 1), newWorksheet.Cells(rowCount, colCount))
            oRange.Value = values

        End If

        theWorkbook.Save()

        Return newWorksheet

End Function
answered Mar 30, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24, 2022 in Database by Edureka
• 13,690 points
1,983 views
0 votes
1 answer

What is the Excel formula to countif text partially makes up a cell?

With Formulas, Countif Partial String/Substring Match We can ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
15,004 views
0 votes
1 answer

excel Copy shapes from one worksheet to another

This will copy all the shapes from Sheet1 to Sheet2: Sub ...READ MORE

answered Mar 31, 2022 in Database by gaurav
• 23,260 points
1,335 views
0 votes
1 answer

Convert Date to Text without losing the format in Excel?

The steps are as follows: Copy the dates ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
37,482 views
0 votes
0 answers

How to split a string of text in excel based on a given word?

I have a list of combinations of ...READ MORE

Apr 4, 2022 in Database by gaurav
• 23,260 points
365 views
0 votes
1 answer

How to type Unicode currency character in Visual Basic Editor

VBA editor is not Unicode. In order to ...READ MORE

answered Apr 5, 2022 in Database by gaurav
• 23,260 points
1,113 views
0 votes
1 answer
0 votes
1 answer

Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,731 views
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,216 views
0 votes
1 answer

Excel TEXT formula doesn't convert 'yyyy' to a year

It appears that if you have a ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
945 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP