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!

Make a little test. From scratch: `dim excelApp = new excel.Application() with { .DisplayAlerts = false, .Visible = true } dim wBook = excelApp.Workbooks.Add(Type.Missing) dim sheet as excel.Worksheet = wBook.Worksheets.Add() Clipboard.SetText(textToPaste, TextDataFormat.Text) sheet.Paste()`. Does it work? No? Then it's what you're trying to paste that is not compatible. — Jimi, Jun 05 '21 at 20:18
Where `excel` is `Imports excel = Microsoft.Office.Interop.Excel`. -- In TaskManager, kill all `EXCEL.EXE` processes you have left around beforehand. — Jimi, Jun 05 '21 at 20:21
I wouldn't be too hard to put that string in a DataTable. The investigate GemBox for an easy import. — Mary, Jun 05 '21 at 20:45
My thanks to @Jimi. I confirmed the clipboard payload is not the problem. There seems to be something amiss with the Worksheet.Paste() interop and I elected to follow a solution suggested here [link](https://stackoverflow.com/questions/5572168/can-you-paste-a-block-of-cells-in-one-shot-using-excel-interop-w-o-using-the-cli?rq=1). I will post my solution below soon. — DMP, Jun 05 '21 at 21:02

DMP · Answer 1 · 2021-06-05T21:40:17.630

So, here is what I ended up doing to solve (actually avoid and solve) the problem I was facing. Here is how I altered the existing function.

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

The change, of course, is to not use the Clipboard at all (which users might appreciate) and assign the "two-dimensional" text array to a Cell range on the Worksheet. The function (yes, I know, ugly with return values and ByRef parameters) is as follows:

    Private Shared Function ExtractTwoDimDataSet(tabAndCrLfDelimitedText As String, ByRef rowCount As Integer, ByRef colCount As Integer) As String(,)
        rowCount = 0
        colCount = 0

        Dim rows() As String
        Dim columns() As String

        rows = Split(tabAndCrLfDelimitedText, vbCrLf)
        rowCount = rows.Length

        For Each line As String In rows
            columns = Split(line, vbTab)
            If columns.Length > colCount Then
                colCount = columns.Length
            End If
        Next

        Dim values(rowCount, colCount) As String

        rows = Split(tabAndCrLfDelimitedText, vbCrLf)
        Dim r As Integer = 0
        For Each line As String In rows
            columns = Split(line, vbTab)
            Dim c As Integer = 0
            For Each cell As String In columns
                values(r, c) = cell
                c = c + 1
            Next
            r = r + 1
        Next

        Return values

    End Function

The end result does what it needs to do and the function above is fairly reusable but I marked it Private as it is not general-purpose, and depends on the vbCrLf and vbTab delimiters.

This is clearly in the spirit of advice from @Mary ...

Thanks for the views and suggestions from stackoverflow folks!

Exception Visual Basic .NET Clipboard Text to Excel Via Interop/Worksheet.Paste()

1 Answers1