Changing Images With VBA

[Coach3131]

I am trying to write a macro where I can change images for a picture file. I can do the insert method, but if I change pictures and run it again, it just inserts the next picture on top of it instead of changing the current image. Can anyone help here?

 

[jolivanes]

If I understand this right, you want to change from Orange to Blue.
If that is wrong, you'll have to explain a little more (for me anyway).

 

[Coach3131]

I have a cell with a URL referenced that inserts an image. For example, www.mysite.com/image1.jpg.

The next time I run it, I may want to reference /image2.jpg. I want the picture to be in the same spot in the report as before but just changed based on the cell reference.

If I use the Insert method, it will just insert the image on top of the old one instead of change it out.

 

[jolivanes]

What is your code to do all of this?

 

[jkoudsi]

Search the sheet for pictures and delete them. Then run the rest of your code.

 

[jolivanes]

Sub DeletePics()
    Dim **** As Shape
    For Each **** In Sheet1.Shapes
        If ****.Type = msoPicture Then ****.Delete
    Next

   'Here the rest of your code

End Sub

If you have some stray pictures floating around, it will delete them also.

Note: It will delete all pictures

 

[Coach3131]

ImageReference = Range("MyImageReference").Value
On Error Resume Next
ActiveSheet.Shapes.Range(Array("MyImagePic")).Delete
Range("ImageLocation").Select
ActiveSheet.Pictures.Insert(ImageReference).Name = "MyImagePic"

I am able to insert and set location this way... there may be a better way to do the error handling so that if a pic doesn't exist, it doesn't break the code. Also, the code above to delete images, is there a way to put a list of the images to change in an array versus deleting every image? Lets say that I have 5 images on a page and 2 are always static... I would only want to delete the 3 that are not static and replace their references versus delete every picture.

 

[Noz2k]

I think you could give the static pictures a name property and use an if statement to say if ****.name <> "name" then ****.Delete

 

[Coach3131]

The code jolivanes gave is providing an object not found error even though I have an image on a page that I am testing the code with.

 

[Coach3131]

Noz2k, do you have an example?