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

Question

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!

Answer 1

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