Transfer pictures from one workbook to another

Question

I would like to build two workbooks. Workbook A contains a list of animals and pictures as raw data; Workbook B contains a list of animals, and could automatically get their corresponding pictures from Workbook A.

If they were numbers or texts rather than pictures, we could connect the two workbooks by external links (to ranges or names), or by Power Query. But it seems that external links or Power Query don't work with pictures?

Does anyone know how connect two workbooks to achieve this? Does anyone know if it is possible to assign a picture to a cell?

maybe export the source images https://stackoverflow.com/questions/18232987/export-pictures-from-excel-file-into-jpg-using-vba then use use the filepath reference to those images in powerquery — horseyride, Nov 22 '21 at 18:24
Maybe you can Copy the Entire Sheet and then Erase the stray Rows — Apostolos55, Nov 24 '21 at 18:28

Brett · Accepted Answer · 2021-11-26T20:03:12.547

You can use a vlookup-type of setup where the picture changes based on the value entered into a cell. There is an explanation here: https://exceloffthegrid.com/automatically-change-picture/

You'll have to use paste as link or link to picture. I personally cannot test this because Office 365 has paste as link disable right now.

EDIT:

I figured out how to work around Excel.

Insert a Pic
Size it down to the desired cell height/width
Don't click on the pic with the mouse, but move the directional keys with the keyboard (i.e., the green selection [] box) to the cell behind the pic.
Control + C to copy, not the mouse
Pastespecial as linked picture

Thank you, Brett, I will try this, and let you know. – SoftTimur Nov 26 '21 at 21:16 — SoftTimur, Nov 26 '21 at 21:16

score 1 · Answer 2 · answered Nov 23 '21 at 00:49

So, I can't see how you can link them directly but you can use VBA to copy the picture from one workbook to another. You may not like that approach but it's plausible in theory.

Your question regarding whether or not a picture is linked to a cell is a firm yes.

A picture in Excel is classed as a shape. You can loop through each shape and determine if it's a picture or not and then from there, you can copy it from one workbook to another into any cell you care to specify.

The cell the picture resides in can be determined from the TopLeftCell property of the shape itself.

You can test that via this (sandboxed) code below.

Public Sub CopyPictures()
    Dim objShape As Shape, objFromSheet As Worksheet, objToSheet As Worksheet
    
    Set objFromSheet = ThisWorkbook.Worksheets("From")
    Set objToSheet = ThisWorkbook.Worksheets("To")
    
    For Each objShape In objFromSheet.Shapes
        If objShape.Type = msoPicture Then
            objShape.CopyPicture
            objToSheet.Range(objShape.TopLeftCell.Address).PasteSpecial
        End If
    Next
End Sub

Open a new workbook and create two sheets, name them "From" and "To".

Copy a few pictures into the "From" sheet and then run the macro.

If you're happy using VBA then the above code will give you a starting point of getting the image from one workbook to another. You just have to fill in the gaps and write the logic around it.

Is it straight forward? Depending the desired outcome, it may very well not be. All possible though with time and effort.

score 1 · Answer 3 · answered Nov 26 '21 at 00:48

1

I know that it's not perfect method, but this might help you.

In Workbook A images should be put on specific cell and perfectly located. e.g. C2:

In Workbook B you need to put an image, you can paste any image, then select the picture and put the link to cell of Workbook A as a formula: =[Book1]Sheet1!$C$2

answered Nov 26 '21 at 00:48

yasu_naka

66
4

1

Thank you, yasu_naka, I will try this, and let you know. – SoftTimur Nov 26 '21 at 21:16

Transfer pictures from one workbook to another

3 Answers3