How to Create a Portfolio Gallery in Excel

Sharing your portfolio with your Clients is a key step in grabbing a new project. Instead of sharing them multiple URLs or sending them big files, share an Excel file with all necessary URLs. When Clients open this file, all URLs will be converted to images automatically.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

To prepare this tool, we need two sheets. Rename sheet1 as “Portfolio” and sheet2 as “Database”. As the name implies, we will display our portfolio on the sheet “Portfolio” and the sheet “Database” will hold Title, Description and Image URLs. As shown in the image, prepare the sheet “Portfolio”.

Let’s prepare the database

On the sheet “Database” add Title, Description, and URL of a image. You can add as many rows as you want. The macro would stop at an empty row. So please make sure that you are not leaving any blank rows in between the list.

Let’s make it functional

Copy the script and add it to a new module in your macro enabled workbook.

Sub Add_Image(passframe As Range, passrange As Range)
    Dim v_pic As Shape
    Dim v_frame As Range
    Set v_frame = passframe
    On Error Resume Next
    Application.ScreenUpdating = False
    Set Rng = passrange
    For Each cell In Rng
        picurl = cell
        ActiveSheet.Pictures.Insert(picurl).Select
        Set v_pic = Selection.ShapeRange.Item(1)
        With v_pic
        .LockAspectRatio = msoFalse
        .Top = v_frame.Top
        .Left = v_frame.Left
        .Width = v_frame.Width
        .Height = v_frame.Height
    End With
Next
Application.ScreenUpdating = True
End Sub

Sub Update_Portfolio()
    Dim lr As Long
    Dim r As Long
    Dim v_title As Long
    Dim v_desc As Long
    Dim v_pic As Long
    Dim v_rinp As Long
    v_rinp = 5
    lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        Sheets("Portfolio").Range("B" & v_rinp).Value = Sheets("Database").Range("A" & r).Value
        Sheets("Portfolio").Range("B" & v_rinp + 2).Value = Sheets("Database").Range("B" & r).Value
        Call Add_Image(Sheets("Portfolio").Range("H" & v_rinp & ":L" & v_rinp + 7), Sheets("Database").Range("C" & r))
        r = r + 1
        If r <= lr Then
            Sheets("Portfolio").Range("N" & v_rinp).Value = Sheets("Database").Range("A" & r).Value
            Sheets("Portfolio").Range("N" & v_rinp + 2).Value = Sheets("Database").Range("B" & r).Value
            Call Add_Image(Sheets("Portfolio").Range("T" & v_rinp & ":X" & v_rinp + 7), Sheets("Database").Range("C" & r))
        End If
        v_rinp = v_rinp + 9
    Next r
End Sub

How does it work?

The macro reads the last used row in the sheet “Database”. From each row, the title, description is copied to the empty placeholder on the sheet “Portfolio”. Image from the URL is downloaded into the workbook and it is made to fit within the range. However, after completing the steps the user has to manually run the macro to update the portfolio. This will not be a good idea if we are sending this workbook to a Client who is about to give us a new project. The workbook should display the portfolio automatically. To make that happen, paste this script into the code window of “ThisWorkbook”. This will call the macro whenever the workbook is opened and the portfolio will be updated automatically.

Private Sub Workbook_Open()
    Call Update_Portfolio
End Sub

Small resolution is better

Try to use images with a smaller resolution. If you use too many huge resolution images, you might end up in a damaged Excel file and need to repair Excel manually with specialized tools.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted Word and outlook recovery software products. For more information visit www.datanumen.com