How to know the original path of an inserted image ?

Copper Contributor

Hi all,
I have a problem with an Excel file. In this file I used the feature "Insert image above cell" and I chose an image from a local path (on my hard drive). So far so good. 

However, if I copy the file to another computer, the image is gone. That's the concept.


Now the problem is that this an auto-generated Excel file from an external program. I know where the images are but I generated multiple reports, modified some of the images and changed the images in some excel files to point to other folders. It works fine on the original computer but I copied the Excel files and the images (or so I thought) to the other computer and some images are missing.

The question is : how do I know the original path of the image so that I can also copy it and re-link it.

 

The additionnal question to that is : I modified the local image for some reason (didn't change the path) and I want to update the shown image to include my modification without closing and opening again the Excel file. Is that possible and how ? But the first question is really  more important because basically I have a new laptop and I need to recover the whole file and images to export it if needed later in time when I won't have the old laptop anymore.

1 Reply

@Arnaud_Guitton 

To determine the original path of an inserted image in an Excel file and to update the displayed image without closing and reopening the Excel file, you can use VBA. Here's how to achieve both:

  1. Determine the Original Path of an Inserted Image

Unfortunately, Excel does not store the original file path of an inserted image. Once the image is embedded, the path information is not retained. However, if the images were linked (not embedded), the path information can be accessed.

If the images were embedded directly and the original paths were not saved in some way, there's no direct method to retrieve those paths through VBA or any built-in Excel feature. It would be advisable in future to keep a log of image paths if they need to be referenced later.

  1. Update the Displayed Image without Closing and Reopening the Excel File

You can use VBA to update an image in an Excel file. Here’s a step-by-step guide to do this:

Step 1: Open the VBA Editor

  1. Press Alt + F11 to open the VBA Editor.

Step 2: Insert a New Module

  1. Go to Insert > Module.

Step 3: Copy and Paste the Following VBA Code

This code will replace an existing image with a new image:

Vba Code is untested backup your file.

 

 

 

Sub UpdateImage()
    Dim ws As Worksheet
    Dim img As Shape
    Dim imgPath As String
    Dim newImg As Shape
    Dim topPos As Single
    Dim leftPos As Single

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Specify the path of the new image
    imgPath = "C:\path\to\your\new\image.jpg" ' Change this to your image path

    ' Loop through all shapes in the worksheet
    For Each img In ws.Shapes
        If img.Type = msoPicture Then
            ' Get the position of the existing image
            topPos = img.Top
            leftPos = img.Left
            
            ' Delete the existing image
            img.Delete

            ' Insert the new image at the same position
            Set newImg = ws.Pictures.Insert(imgPath)
            newImg.Top = topPos
            newImg.Left = leftPos
            
            ' Optional: adjust the size of the new image to match the old one
            'newImg.Width = img.Width
            'newImg.Height = img.Height

            Exit For ' Assuming there's only one image to replace
        End If
    Next img

    MsgBox "Image updated successfully!"
End Sub

 

 

 

Step 4: Run the Macro

  1. Close the VBA Editor (click the X or press Alt + Q).
  2. Press Alt + F8 to open the Macro dialog box.
  3. Select UpdateImage and click Run.

Notes:

  • File Paths: Ensure the imgPath is correct and points to the new image you want to insert.
  • Sheet Name: Change "Sheet1" to the name of the worksheet where your image is located.
  • Position and Size: The new image will be placed at the same position as the old one. Uncomment and adjust the size lines if you need the new image to match the size of the old image.

Additional Considerations:

  • Logging Paths: For future reports, consider logging the original paths of images in a hidden worksheet or a metadata section within your workbook.
  • Linked Images: If you plan to move workbooks and need to retain image paths, consider linking images rather than embedding them. Linked images retain their file paths and can be updated if the paths remain valid on the new system. However, linked images require that the path remains accessible and unchanged.

By following these steps, you can manage your images more effectively in Excel and ensure they remain updated and correctly linked when moving files between different systems.

 

Excel Add-ins and other optons

There are third-party Excel add-ins that might help manage and update images.

Or using Power Query, Workflows, Built-In Features, etc.

 

Conclusion

While VBA provides a powerful and automated way to handle images in Excel, there are several other methods you can consider based on your comfort level with coding, the complexity of your task, and the frequency with which you need to update images. Whether it's manual management, using add-ins, or linking images, choose the method that best fits your workflow and requirements.

The text, steps and code were created with the help of AI.

 

*I also add this file that I found in my old archive, created by Karin (Beverly) http://Excel-Inn.de

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.