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!