How to change inserted image source?

[bobsan42]

Hello folks
Sometimes I bump into a wall, head first...
Here's the case. I have created a file for preparing specifications for production of structural reinforcement bars.
Since there are a lot of possible shapes i have made an WMF image library (folder) and then insert the WMF image I need into the sheet where i need it using this code:

[B]ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\ArmirovkaImages_v3\" & iimagename & ".wmf").Select[/B]

along with some adjustments for size, position, rotation etc.
So far so good it works flawlessly. Just needed some adjustments for position in Excel 2007.
However - correctly displaying the images later on depends on the correct location of the folder, containing the source images in WMF format.
Since the file (or a number of its copies) is in circulation and is normally used on several different computers, the connection to this folder is sometimes lost and the images don't display correctly or most of the times at all (Image not found).

There are normally 30 to 300 images/shapes in a sheet.
What i want to find out how to do is:
a) Where is excel looking for the source images once they are inserted
and/or
b) how to change the source of all already inserted images


thanks for any help in advance.

 

[yky]

Can you use shapes.addpicture method? This method adds pictures, not links, to the sheet. So, the pictures can be displayed independent of the original picture location. The file size will be larger because pictures are now stored in the file.

 

[bobsan42]

Well, i guess i could, but the file gets heavier as it is.
I will probably try this method during the next days and look for pros and cons in my case.

But i would very much like to solve the problem as i described it.

 

[bobsan42]

Any ideas? Anyone?

 

[bobsan42]

Well, after a lot of digging this looks more and more like a dead end.
Seems to me that the pros of yky's suggestion beat the cons.
The WMF images contain relatively simple vector graphics so each one is not larger than 2 KB. Given the freedom of distribution this gives me I decided i can live with splitting (starting a new) workbook every once in a while (when the old one become too heavy).

So finally i decided to give it a try and this is what i came up with for embedding images into the workbook:

Public Sub insSelectedImage(iimagename As String)
Dim r As String
Dim myHeight As Integer
Dim s As Object
r = ActiveCell.Address
On Error GoTo Kraj

myHeight = 65

Set s = ActiveSheet.Shapes.AddPicture(ThisWorkbook.Path & "\ArmirovkaImages_v3\" & iimagename & ".wmf", True, True, ActiveCell.Left, ActiveCell.Top, 100, 100)
s.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
s.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue
s.LockAspectRatio = msoTrue
s.ScaleHeight (myHeight / s.Height), msoTrue
s.IncrementLeft (250 - s.Width) / 2
s.IncrementTop 1

If UserForm1.CheckBox1 = True Then s.Flip msoFlipHorizontal
If UserForm1.CheckBox2 = True Then s.Flip msoFlipVertical
If UserForm1.CheckBox3 = True Then s.IncrementRotation -90
If UserForm1.CheckBox4 = True Then s.IncrementRotation 90
If UserForm1.CheckBox5 = True Then
    s.IncrementRotation -90
    s.IncrementRotation -90
End If

Kraj:
Range(r).Offset(2, 3).Select
End Sub

What annoys me though is that once the link with the source image is lost excel gives you the option to "Change the image..." in the context menu.
But Macro recorder captured only the image selection of the whole process and nothing more - i guess this goes beyound SQR("Advanced")

However - i wrote this post only to complete the thread - just in case someone reads it in the future.
Sill - if anyone happens to come around and have any brighter ideas or improvements - I am always open to new suggestions.