Excel Pictures - change filename
How do you determine the file name of a picture inserted into an Excel worksheet please?
I have a task which is to change the images in about 700 workbooks (this is a company logo)
Each time my client has a new client they have to manually insert the new logo on each sheet...
So I made a little c#/VBA app to do this - but I cannot determine the original file path the excel picture for the image.
So i have resorted to iterating the worksheet pictures collection and seeing if each picture is located in a like cell range.
(the name is somewhat random)
When I find I set the name to one I can find like "Company Logo" - so next time I can find it.
I see sometimes when I add a picture the original file name is in the "alt text" field. I s this reliable?
Surely there must be a better way.
Incidentally the picture name is easy to get at programatically but I have not found a way to to do that manually - else I could the company (my client) to set up the "Company Logo" name manually for each correct picture - any ordinary excel user could then do it - then it would be easy for me to update!
Surely there must be a way to determine the picture's original file name?
Ideally I would like to have made it so that changing the logo would simply be a case of changing the file - in one location - i.e. have each picture reference the picture file (e.g. Company Logo.bmp, .png etc) - but I suppose that has a side effect that the picture file would have to accompany the excel spreadsheets else if the sheet is moved or copied it wont find the picure file .... Aghhhh! Help!
Update shapes
If the location of the picture in Excel is defined and fixed and you know the picture file to replace it with, this can be automated.
1. Loop through all the shapes.
2. Check if the shape's topleft cell is the one you are looking for.
3. If yes, delete the shape and insert the new shape; and resize it.
4. Name the new shape as you want. Alternatively, you can also set its AlternativeText property programatically - ThisWorkbook.Worksheets("Sheet1").shapes("YourImage").AlternativeText="Shape1"
Ah yes - good idea
Thanks Vishesh great to hear from you.
Yes I think the alternative text is the way to go here.
The issue is I do not always know either where the the picture is or its name. (The Excel sheets' logo pics were originally hand done so there is no reliable way of knowing anything.
Usually (80%) of the time it is in one range of cells
From what you say I am hoping that the alternative text will holds the original file name - and that would be enough to identify the correct picture (the sheets usually have at least 2 - and sometimes more than 1 of the one i need to replace) to replace it in the Pictures collection. I can then easily get the cell range it occupies: Picture.TopLeftCell(), Picture.BottomRightCell().
I want to see if the alternative text holds the original file name - as it seems to when I create picture items experimentally in Excel. Certainly I think it is a very good bet that the person creating the forms originally would have used 1 picture file for the company Logo - i will rush back and try!
Once I run my tool over the 700 or so workbooks - it will give the correct pictures a name like "Company Logo" - so it will be easy to find and replace the logos next time....
Many thanks Vishesh - much appreciated!
Kind regards
Terry