Refreshing Image links VBA Excel

Hi,
I am making a sheet where a user enters 8 pictures on a page, the images are inserted using a button with the following code:

Please Login or Register  to view this content.

Basically, I am having problems with the images, in that some of the images sometimes give the message "The linked image cannot be displayed. The file may have been moved, renamed or deleted. Verify that the link points to the correct file and location." I get that most of the time due to renaming folders etc. But a couple of times I have had it randomly lose track of images with no explanation. I realised that simply deleting the image and then pressing undo resets the link somehow but don't know how I would do this manually.

I was basically hoping to find a way to make the links search relative to the excel sheets location. For example, if the image was initially stored in C:\Users\User\Documents\Img1234, but then moved into a new folder S:\SharedFolder\User\JobName\Images\Img1234. The images will always be moved somewhere in the "JobName" folder, normally in the images folder. But just in case, I was thinking maybe to get some module/function to search the whole folder where the excel sheet is saved for the linked Image, to replace the link. The problem is, I don't know if excel stores the path links anywhere that I can access. I have searched all day on the internet and haven't gotten anywhere. Probably my best idea is to store each images file location on another sheet when it is imported and then code for the "Change Picture" function with the associated filename.


The potential problem with this method is I would want to loop through the images on each worksheet to find any where the links need refreshing an I don't know if there is any way of identifying which ones are broken. I would want this to run every time you open the spreadsheet. Could someone let me know if this is a method that would even work and is it the most efficient method? and if its not could you attempt to point me in the right direction please.

Thanks in Advance for any responses

Wouldn't it be easier to copy the files to a local folder?

You mean just make sure everything is kept in the same place relative to the excel sheet when transferred to the shared folder? I would do that, but not only am I giving it to users who are likely to not follow that even if I tell them to, but it seems to occasionally do it randomly anyway even if the files are kept in the exact same places.

It's not losing track of them randomly exactly, but I once found that one of the images had vanished and come up with that message "The linked image cannot be displayed..." and it immediately came back when I deleted the image and then pressed undo, almost as if it hadn't refreshed the link, rather than lost track of it.

I agree with Ali, you need the path to be constant - Especially as trying to work with Excel and a Network drive can be a pain. All kinds of problems can arise.

For starters, I'd recommend on not using the Drive letter (S) and using the network name.

Most users where I work have the letter G assigned to a shared drive, but some have it as X, Y, or Z - To get round that I use the network name of the drive which looks like \\GPBWFCD0QP\

It might be that the drive letter S is actually assigned to a subfolder within the network drive, so S might point to \\GPBWFCD0QP\UserSharedFolder\ which you'll need to use.

Another problem which you might encounter and might not be realising is that if Excel can't find a Network drive location, it'll default to the My Documents folder on the local machine. If you're already using that then you might not be noticing that something is going wrong.

Other problems can arise if you're trying to use the Excel workbook from a Network drive, problems can arise when setting your folder defaults unless your specific there as well.

I've had all kinds of fun with this kind of thing in the past :-|