Copy picture from userform to spreadsheet

I have a picture loaded into an image control contained within a userform. I would like to copy the picture from the userform image control and paste it into a spreadsheet. I've found a means to create an OLEObject within the spreadsheet and move the image that way here , but I'm creating multiple spreadsheets and I don't want all the extra objects.

If I go into the VBA Editor, into the userform, into the image control, and using my mouse, select the (Bitmap) in the Picture property and copy it, I can paste just the picture into a spreadsheet.

If I use the macro recorder to do the same, the code naturally only includes the select and paste methods. And if I reference the same picture property within code, all I get back is the handle.

I have searched extensively, and I believe exhaustively, and I can't find any means of programmatically grabbing the handle and pasting the picture in VBA. I'm fairly new to VBA as it is and API level work is well beyond my current abilities.

Solution

You can export to a temporary file and load from there:

Private Sub UserForm_Activate()

    TransferToSheet Me.Image1, Sheet1

End Sub

Private Sub TransferToSheet(picControl, sht As Worksheet)
    Const TemporaryFolder = 2
    Dim fso, p
    Set fso = CreateObject("scripting.filesystemobject")
    p = fso.GetSpecialFolder(TemporaryFolder).Path & "\" & fso.gettempname
    SavePicture picControl.Picture, p
    sht.Pictures.Insert p
    fso.deletefile p
End Sub