CODEWITHSUNDEEP
excelvba
Baineteo
Baineteo

Reputation: 31

Excel-vba: Pasting Image only from Clipboard + allowing for re-use later

I would like to do allow the user to do following in excel. This would probably be achieved using macros.

  1. User takes a screenshot, like with Snipping Tool
  2. Code checks if the clipboard contains an image or not (code only to run only when clipboard holds an image)
  3. Code will then paste the image from clipboard onto a specified area (e.g. Cell J55).
  4. At the same time, I want to give this pasted image an ID (say, imgSource1), so that the user can later re-use this image on another sheet (say, on sheet 2, call for imgSource1 and paste it in there)

I've gotten thus far: learnt how to paste something into excel at the said location. I haven't been able to find a working code for checking if the clipboard is holding an image or not. Now I need to figure out how to paste only image (how to check clipboard is holding only image before pasting). The following code doesn't seem to work for me. 

Sub btn_addImg1()
If (Clipboard.GetImage() != null)
    Sheet1.Paste Destination:=Range("J55"), Link:=False
Else
    'do nothing
End If

The "If (Clipboard.GetImage()" line is red, and it's telling me it needs a ")" at "!=".

Note that saving an image on a local drive is not a feasible solution for my situation. It has to be pasted from the clipboard.

Thank you!

Upvotes: 2

Views: 3423

Answers (2)

One In The Net
One In The Net

Reputation: 29

The Limak's solution works, but only for individual cells::

Sub PasteImageFromClipboard ()
Dim datObj As New DataObject 'Requires Microsoft Forms 2.0 Object Library

On Error Resume Next

With datObj
    .GetFromClipboard
     Err.Clear
     text = .GetText
End with

If Err = -2147221404 Then .Paste Destination:=Activecell, Link:=False

End Sub 'PasteImageFromClipboard

Upvotes: 0

Limak
Limak

Reputation: 1521

I figured out some workaround, but it need testing.

Btw, you need to go Tools -> References -> Microsoft Forms 2.0 Oject Librarty to make MSForms working. 

    Sub btn_addImg1()
    Dim DataObj As New MSForms.DataObject
    DataObj.GetFromClipboard
    On Error GoTo Img
        GetClipboardText = DataObj.GetText
    On Error GoTo 0
Img:
    If Err = -2147221404 Then
        Err = 0
        Sheet1.Paste Destination:=Sheet1.Range("J55"), Link:=False
    Else
        'do nothing
    End If
    End Sub

Upvotes: 3

Related Questions