Add photo in excel spreadsheet from local folder

Question

I have a spreadsheet in Excel with a list of people. For each person I have a code. In a local folder I have photos of these people and the file name of the photos is the code of the person.

Do you have a formula or something that I can use to make Excel go in the folder to find each person’s photo automatically? I need the photos to appear on the line with that person’s data.

Thanks for your help.

Answer 1

As far as I know there isn’t a ready-made formula that can do this. But you have a few options.

Option 1

The simplest option and that does not involve VBA (uses only existing formulas and functions in Excel "basic") requires you to manually add each of the images in any spreadsheet, define names for each cell containing the images and use these names to make the reference indirectly. I will not play this solution here because it was not what you requested directly and because it has the main problem that you need to add the images manually and build a huge formula that is nothing less than a chained sequence of calls to SE, which begins to become impracticable if you have more than a dozen codes. But you can find this solution on this site of "Excel Tips" (in English).

Option 2

The second alternative, which is the solution that I propose, requires the use of VBA and that your spreadsheet is enabled to execute macros. But it is much better because it automatically adds the images from the disk as you want, and ensures that the images are always positioned correctly in the cell also automatically.

To do this, open the developer window (using the Alt+F11 shortcut or adding the "Developer" option to the button tape) and add a new module. For example:

Then enter the code of the following function in the module created:

Public Function getImage(ByVal sCode As String) As String

    Dim sFile As String
    Dim oSheet As Worksheet
    Dim oCell As Range
    Dim oImage As Shape

    Set oCell = Application.Caller ' Célula onde a função foi chamada
    Set oSheet = oCell.Parent      ' Planilha que chamou a função

    ' Procura por uma imagem existente identificada pelo código (que precisa ser único!)
    Set oImage = Nothing
    For i = 1 To oSheet.Shapes.Count
        If oSheet.Shapes(i).Name = sCode Then
            Set oImage = oSheet.Shapes(i)
            Exit For
        End If
    Next i


    ' Se ainda não existir uma imagem com o código, carrega do arquivo e cria-a.
    ' A imagem já é posicionada na exata posição da célula onde a função foi chamada.
    If oImage Is Nothing Then
        sFile = "c:\temp\sopt\" & sCode & ".jpg"
        Set oImage = oSheet.Shapes.AddPicture(sFile, msoCTrue, msoCTrue, oCell.Left, oCell.Top, oCell.Width, oCell.Height)
        oImage.Name = sCode

    ' Caso contrário, se a imagem já existir, garante que ela se posiciona e cabe exatamente dentro da célula
    ' (apenas para o caso do usuário ter movido ou redimensionado manualmente a imagem sem querer)
    Else
        With oImage
            .Left = oCell.Left
            .Top = oCell.Top
            .Width = oCell.Width
            .Height = oCell.Height
        End With
    End If

    ' Retorna nada para a célula (afinal, esta é somente uma função de auxílio)
    getImage = ""

End Function

Do not forget to save the spreadsheet with the extension . xlsm (Folder Excel Macro Enabled Work).

Then, in the Excel cells where you want the images to be displayed, use the formula:

=getImage(<CÓDIGO>)

For example, suppose in cell B4 you have the code of an image. To automatically add the image of this code to cell C4, add the formula to it =getImage(B4). Here is an illustration of the result:

The function code is commented to help in understanding, but basically it works as follows:

1. It takes as a parameter the code, which can come from any source (it can be a fixed value or it can come from a cell, as in the example above).

2. Based on this code, and on the call tab (spreadsheet) (note that this is different from the active spreadsheet! This function may be running in the background by Excel for another tab that does not activate it! ), the function loads the image only if it does not yet exist (preventing the same image from being loaded multiple times and overloading your Excel file).

3. Based on the call cell (the cell where the function was called), the function also adjusts the position of the image and its size as a form of simulate that the image is inside the cell. Note that Excel does not allow to have cell values that are images, so this "simulation".

4. Finally, the function returns (returns) an empty string. This is necessary because it is used as a "formula" in Excel, despite making an indirect manipulation to the cell content.

Note that this function is able to ensure that the image is automatically resized as you adjust the size of the cells, but for that the function needs to be reevaluated! For performance reasons, Excel does not re-run every formula every time you resize cells. So, if you resize them, you need to call the functions again - one by one, editing the cell and pressing ENTER, or (much better), simply asking Excel to reevaluate all formulas with the shortcut Ctrl+Alt+Shift+F9.

Editing

The code I made does not pull any image, it literally generates error (and Excel to the program) if it does not find the image. You must have done something different than I suggested.

Anyway, instead of displaying nothing, maybe it is better to have a specific image to indicate non-existent photos, so that the user realizes that it was not just any system error. Use something like a user image (like this one here). The code is simple to change. Just tell the VBA to continue running in case of error (note the line On Error Resume Next added at the very beginning of the function) and check if the returned image object is empty (that is, if it is equal to Nothing). If it is, you force the loading of the default image you are going to use. Example of changed code:

Public Function getImage(ByVal sCode As String) As String

    On Error Resume Next ' Indica que no caso de erros de carregamento de imagem deve continuar executando a partir da próxima linha

    Dim sFile As String
    Dim oSheet As Worksheet
    Dim oCell As Range
    Dim oImage As Shape

    Set oCell = Application.Caller ' Célula onde a função foi chamada
    Set oSheet = oCell.Parent      ' Planilha que chamou a função

    ' Procura por uma imagem existente identificada pelo código (que precisa ser único!)
    Set oImage = Nothing
    For i = 1 To oSheet.Shapes.Count
        If oSheet.Shapes(i).Name = sCode Then
            Set oImage = oSheet.Shapes(i)
            Exit For
        End If
    Next i


    ' Se ainda não existir uma imagem com o código, carrega do arquivo e cria-a.
    ' A imagem já é posicionada na exata posição da célula onde a função foi chamada.
    If oImage Is Nothing Then
        sFile = "c:\temp\sopt\" & sCode & ".jpg"
        Set oImage = oSheet.Shapes.AddPicture(sFile, msoCTrue, msoCTrue, oCell.Left, oCell.Top, oCell.Width, oCell.Height)

        If oImage Is Nothing Then ' Verifica se falhou o carregamento da imagem. Se falhou, adiciona a imagem genérica (com nome fixo)
            Set oImage = oSheet.Shapes.AddPicture("c:\temp\sopt\inexistente.jpg", msoCTrue, msoCTrue, oCell.Left, oCell.Top, oCell.Width, oCell.Height)
        End If

        oImage.Name = sCode

    ' Caso contrário, se a imagem já existir, garante que ela se posiciona e cabe exatamente dentro da célula
    ' (apenas para o caso do usuário ter movido ou redimensionado manualmente a imagem sem querer)
    Else
        With oImage
            .Left = oCell.Left
            .Top = oCell.Top
            .Width = oCell.Width
            .Height = oCell.Height
        End With
    End If

    ' Retorna nada para a célula (afinal, esta é somente uma função de auxílio)
    getImage = ""

End Function

Result it generates (with the default image called "inexistent.jpg" and kept in the same directory as the others - note that I literally erased the images of the eagle and fox to generate the "error"):

Note: this code is very simple and serves as an example. Note that it checks whether an image with the code already exists and so not the recharges if it already exists (to improve performance). This means that if a user did not have an image and then the spreadsheet loaded the generic, if it then create an image the spreadsheet will not update properly. You will need to manually delete the Excel image and update to the correct image.

Finally, note that if you still want to show nothing if the image does not exist, just keep the first line of code (the on Error Resume Next), verify that the error has occurred and do nothing. In this case you wouldn’t even have to worry about the comment in the paragraph above, because if eventually the image appears the code will adjust itself. Only that simply not showing anything to the user is a bad usability because it can cause confusion (he will notice that "nothing happened", but will not necessarily understand that his image is missing).