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.
If you want to start to use the software as soon as possible, then you can:
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.
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