Excel PasteSpecial function pasting as image

Question

I am trying to copy formatted cells from existing excel sheet to new sheet in vb.net(2010) using pastespecial method but it is pasting the cell values as picture rather than text. Any help in this regard would be highly appreciated:

    Public Sub set_header(ByVal r As Integer, ByVal c As Integer, ByVal dt1 As Date, ByVal dt2 As Date, ByVal sheetname As String)

    Dim wbCopy As Excel.Workbook
    Dim wsCopy As Excel.Worksheet
    Dim rngCopy As Excel.Range
    Dim wbPaste As Excel.Workbook
    Dim wsPaste As Excel.Worksheet
    Dim rngPaste As Excel.Range
    Dim xlsApp As Excel.Application
    xlsApp = New Excel.Application
    wbCopy = xlsApp.Workbooks.Open("C:\Windows\rta2.xlsx") 
    wsCopy = wbCopy.Worksheets(sheetname)
    rngCopy = wsCopy.Range("A7:O9")
    Dim addr As Object
    addr = ExcelSheet.Cells(r, c).Address(False, False)
    rngPaste = ExcelSheet.Range(addr) 
    rngCopy.Copy()
    rngPaste.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,false,false)
    ExcelSheet.Cells(r + 2, c + 4) = "from " & dt1 & " to " & dt2
    Set wbCopy = Nothing
    Set wsCopy = Nothing
    Set rngCopy = Nothing
    Set wbPaste = Nothing
    Set wsPaste = Nothing
    Set rngPaste = Nothing
    ExcelApp.Quit
   End sub

Thanks

How about when you set a value to rngPaste. Try Set rngPaste = ExcelSheet.Range(addr) — Wally, Aug 02 '13 at 12:20
@pnuts actually it should be string because address returns string containing cell alphanumeric address like "C4" etc — kashif, Aug 02 '13 at 12:42

score 0 · Answer 1 · answered Jun 09 '17 at 02:41

I have the same problem. I first check to see if content of clipboard is in excel format. If yes, I will use Excel's PasteSpecial as Value If no, I will paste value from clipboard with MSForms.DataObject

below is my code, you can see if it solve the issue:

' Code Block to prevent pasting Images with PasteSpecial
If IsClipboardContentInExcelFormat Then
    Selection.PasteSpecial Paste:=xlValues
Else
    Selection.Value = GetClipBoardText           
End If    


Function GetClipBoardText()
   ' src: http://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard
   Dim DataObj As MSForms.DataObject
   Set DataObj = New MSForms.DataObject '<~~ Amended as per jp's suggestion
   Dim myString As String

   On Error GoTo ErrorHandling

   DataObj.GetFromClipboard '~~> Get data from the clipboard.
   myString = DataObj.GetText(1)
   GetClipBoardText = myString

   Exit Function
ErrorHandling:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Function


' Will use MSForms.DataObject for geting clipboard content
' reference: http://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard
' reference: http://msdn.microsoft.com/en-us/library/office/ff839748%28v=office.14%29.aspx
Private Function IsClipboardContentInExcelFormat() As Boolean
    IsClipboardContentInExcelFormat = False

    On Error GoTo ErrorHandling

    Dim aFmts, fmt
    aFmts = Application.ClipboardFormats

    For Each fmt In aFmts

        If (fmt = xlClipboardFormatBIFF _
             Or fmt = xlClipboardFormatBIFF12 _
             Or fmt = xlClipboardFormatBIFF2 _
             Or fmt = xlClipboardFormatBIFF3 _
             Or fmt = xlClipboardFormatBIFF4 _
             ) Then
            IsClipboardContentInExcelFormat = True
            Exit Function
        End If
    Next

    Exit Function

ErrorHandling:
    If Err <> 0 Then MsgBox "Cannot Read Data in Clipboard"

End Function

score 0 · Answer 2 · answered Jan 25 '22 at 15:55

For anyone like myself that Googled this issue.

When I was pasting my values it was showing up in the new Workbook as an image as well. I found that I could control the formatting of the paste through Excel.XlPasteType.xlPasteValues

Dim TestRange = SourceExcelSheet.Range("B4:I4")
    TestRange.Copy()
    TargetExcelSheet.Range("B4").PasteSpecial(Excel.XlPasteType.xlPasteValues)

Excel PasteSpecial function pasting as image

2 Answers2