none
Identify and change path to linked pictures in Excel 2010 using VBA RRS feed

  • Question

  • I have manually pasted a large number of linked pictures into a 2010 Excel spreadsheet using the insert picture -> select picture location --> link to file. For context, these pictures are .PNG files, and their contents are maps showing change in sales regionally, and together form part of a report. I update the pictures using R each quarter (keep the name the same but update the map), and my report automatically updates when I reopen the spreadsheet. Perfect, or at least I thought so.

    I now need to change the directory where the plots are kept, and need to update the links. The pictures show up in the name box as Picture 8, Picture 9 and so on and I can't fathom out the picture's underlying file path using VBA (there are around 200 pictures).  Now if I recorded a macro to insert the pictures Excel would use ActiveSheet.Pictures.Insert(filename).select. I can write code to loop through all my pictures but I haven't found a way to back out the filename. Any idea how I can see the underlying file location so I might change it - the reference has to be stored somewhere otherwise the spreadsheet wouldn't update when I change the contents.

    I have tried posting this on StackOverflow, but I am no closer and wonder if anyone else can help? I know I could do this manually in less time, or rebuild the spreadsheet but that would be admitting defeat. 

    Thursday, May 12, 2016 9:21 AM

All replies

  • Hi N1DRR,

    did you mean you want a VBA code of inserting an image using linktofile and at that time you want to get the path of an inserted image.

    if so then please visit the link below it will help you to inset an image in Excel then you have to just print a path in your excel sheet.

    Shapes.AddPicture Method (Excel)

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 13, 2016 6:39 AM
    Moderator
  • Thanks, but no, that's not what I want to do. The pictures are already there. But they are linked files. I want to find out what the filepath of those linked files is, and change it. For example, if I have an Excel file, and I insert a linked picture using say ActiveSheet.Pictures.Insert("C:\pictures\mypicture.png").select, and I physically move that picture to say "C:\production\mypicture.png", when I next open up Excel, my picture will be replaced by a box with a red cross and a message saying the file has moved or been deleted. Now all I want to do is find, for each picture, where Excel is picking up the source of that file and change it so that Excel points to the new location.
    Friday, May 13, 2016 10:54 AM
  • Re:  displaying pictures

    A different approach...
    Use the FireFox browser and enter the new folder path in the address bar.
    A list of all files in that folder is generated - copy the list and paste into an Excel worksheet.
    That list in Excel will have a hyperlink to each file in the folder.  Clicking on a link displays the picture in your picture viewer.

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
    free & commercial excel programs / workbooks
    • Edited by James Cone Wednesday, September 21, 2016 11:37 PM
    Saturday, May 14, 2016 12:58 AM
  • Hi, N1DRR,
     it looks like we are not able to get the file path of linked image.
     Recording a macro is also the displaying the line you have mentioned.

     ActiveSheet.Pictures.Insert("C:\pictures\mypicture.png").select

    it giving the path but line of code is not for linked image.

    I think the work around is to save a path in cell at the time of inserting an image.

    but I want to confirm from you that why you need to get the path of an image. because when you change the image it needs new path not old one.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 17, 2016 8:26 AM
    Moderator
  • Thanks for the note. I am probably not being clear.

    Imagine the scenario. You have manually imported a lot of linked pictures into a spreadsheet, neatly arranged them to go with corresponding information and graphics to create a report. The pictures you imported are colour keyed thematic maps (all saved as .png files) which show at postcode resolution how your sales changed between Q3 2015 and Q4 2015. For argument sake you've called them Picture1.png, Picture2.png, ... and they are on the J:\ drive. Now in Q1 2016 you run some new code (this is in R) and replaces Picture1.png, ... with a new Picture1.png, ... Now this process does not break links in Excel which are imbedded somewhere so when you reopen your Excel spreadsheet, the spreadsheet contains all the new pictures, ready for you to print off the report ready done and send it off to finance. Job done.

    Now some bright spark says the report is great and we better put it in the production environment. This is on a different drive. Unfortunately the spreadsheet links to pictures on the J:\ drive and so if I move them, I get a bunch of red X's and an error telling me they aren't where Excel thinks they should be.

    The pictures are already linked in the spreadsheet so I don't want to re-import. All I want to know is how do I change where Excel looks for the pictures with a neat piece of VBA. So if Excel is picking up the plots from J:\picture1.png, ... , how do I re-point Excel to K:\picture1.png, ...

    The reason for putting in the ActiveSheet.Pictures.Insert line was just to try to give a pointer - i.e. if your mate used ActiveSheet.Pictures.Insert(x).select to insert a picture and asked you to find out x, how would you do this with vba? Currently I can't do this bit, and it might be a red herring, but it might also suggest a way to point Excel in the right direction. 

    Thanks

    Paul

    Wednesday, May 18, 2016 4:59 PM
  • Hi N1DRR,

    as we are not getting the path from already inserted linked images the work around is as follows.

    you can place your images links in different links and you can save the paths in different sheet.

    and use code below.

    Dim url_column As Range
    Dim image_column As Range
    
    Set url_column = Worksheets(1).UsedRange.Columns("A")
    Set image_column = Worksheets(1).UsedRange.Columns("B")
    
    Dim i As Long
    For i = 1 To url_column.Cells.Count
    
      With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
        .Left = image_column.Cells(i).Left
        .Top = image_column.Cells(i).Top
        image_column.Cells(i).EntireRow.RowHeight = .Height
      End With
    
    Next
    

    For more information Regarding links in Excel. please visit the link below.

    Description of link management and storage in Excel

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 19, 2016 7:44 AM
    Moderator
  • Have you checked the Alternative Text of the Pic. Right Click Pic - > "Size & Properties

    If linked then it gives the filepath of image already stored. But this will not help in replacing images.

    If Embedded then it gives filename of Image

    ******Work around.

    For each pics->Get the File Name of pics (From Alt Text) and Coordinates etc -> Append the file name to new path -> Insert pics with new pathname and coordinates.



    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, October 4, 2016 6:58 AM
    Answerer