Excel – link images using a relative path

microsoft excelmicrosoft-excel-2008microsoft-excel-2011

I am working on an MS Excel workbook that contains around 200 images. They are currently saved within the workbook, so the file becomes huge and working gets very slow.

Linking the pictures without saving them works very well — I now have the Excel document and a folder "pictures" next to it that contains all my image files.

However, when I move the document and the folder to a new location, all my pictures disappear. This seems to be because Excel saves the link information as absolute paths. (Update: Actually, according to this thread, Excel stores the link information as relative paths as well. Now I really don't know why my links break down..)

Is there a convenient way to save them as relative paths or have Excel automatically update the path information?

Update: It's important that the images get displayed on the sheet and can be printed.

I am working with Microsoft Excel for Mac 2008 and 2011.
I really appreciate your help.

Best Answer

You have 2 basic questions here, rendering images in Excel, and relative paths.

Rendering Images in Excel

There's a script to accomplish this on SO:

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

Relative Paths

I don't know of a way to specify relative paths in an Excel doc, but there's an easy workaround for this. Create a folder for you Excel doc and all of the supporting images, and keep them together. If you need to move this to another location, you just move the whole folder. This also makes it easy to zip up & send to others.

Related Question