inserting an image in a spreadsheet using VBA

Question

I am trying to write some VBA behind an excel spreadsheet and I have run into the following problem:  I want my code to insert an image (a .png file) into a spreadsheet.  I tried recording myself (excel macro) inserting the image, so I could see the code that Excel generated, but then when I used that code in the VBA I was writing (see below) it did not work.   

The code I recorded:

Range("B2").Select

    ActiveSheet.Pictures.Insert("C:\Tecplot\Working\Images\image 1.png").Select

 I transferred this to my VBA as follows:

mysheet.Pictures.Insert(newfname).Select

   where  newfname  is a string containing the desired path and filename.  

I get the following message when I try this: 

"Unable to get the Insert property of the Pictures class" 

Does anyone know how to fix this?      

 I am using Excel 2010 (part of MS Office 2010)

Answer 1

You need to determine its Name or Number:

Sub ShapeLister()

    If ActiveSheet.Shapes.Count = 0 Then

        MsgBox "No Shapes"

        Exit Sub

    End If

    msg = ""

    i = 1

    For Each s In ActiveSheet.Shapes

        msg = msg & i & vbTab & s.Name & vbCrLf

        i = i + 1

    Next s

    MsgBox msg

End Sub

Answer 2

Thanks for the quick reply.

I have to produce 200 letters each with a different signature and save each letter to its own PDF file.

There are 2 pictures in the letter.   The first is the company logo; the second is a signature. My macro inserts the signature and saves the letter to the PDF file correctly.  

I want to delete the signature after the the letter is saved to the PDF file.  Deleting the signature is my problem.  The following code is not working:

     ActiveWorkbook.Worksheets("CovLtrQuarterly").Activate

     ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Delete

The second statement produces a run-time error 1004

Delete method of Picture class failed.

Earlier today I tried ActiveSheet.Shapes(1).Delete -  it deletes the company logo.

Any help would be greatly appreciated.

Answer 3

If the Picture is the only Shape on the worksheet, then this should delete it:

Sub PictureDeleter()

ActiveSheet.Shapes(1).Delete

End Sub

If there is more than one Shape on the sheet, determine its number and delete it.

Answer 4

The following statement inserts an image into a spreadsheet:

mysheet.Pictures.Insert(newfname).Select inserts

What code should be used to delete newframe?

Answer 5

I don't see where newfname or mysheet are defined.  This works for me:

Sub PictureInserter()

Dim mysheet As Worksheet

Set mysheet = ActiveSheet

newfname = "C:\TestFolder\Untitled.bmp"

mysheet.Pictures.Insert(newfname).Select

End Sub

---

gsnu201101