Excel changing UNC paths in shared workbook

I have never done this before, but this link seems to be what you are looking for. While it is not as easy as doing it for all of those other data sources, it can be done.

All that needs to be done is enclose the path to the workbook in single quotes, and then append the standard structure for getting specific sheet from that workbook.

Example: =Sum('c:\excel_files\[test.xls]sheet1'!a1:a100)

Is the basic structure that it seems that you should be using. Note that these references will not update in real time, the file must be reopened or otherwise refreshed.

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.