Insert Student Pictures into Excel Cells with VBA
Question details
Insert each student's JPG picture beside the matching name and resize it to fit within the target cell.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Student names are listed in column B, and matching JPG files are stored in one folder.
- Observed behavior
- Each matching picture should appear in column A on the same row, retain its aspect ratio, and leave the cell borders visible.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) in the same folder as the JPG files. Make sure each filename matches the name in column B, excluding the .jpg extension, and adjust the target column width and row heights to provide enough space.
Use a VBA macro to insert and fit the matching pictures
Use this method to process the names in column B and place each matching JPG in column A.
The macro uses the worksheet that is active when you run it. It inserts pictures as floating shapes positioned over cells, rather than as Excel's newer in-cell picture type.
In desktop Excel, right-click the worksheet tab and select View Code. In the VBA editor, select Insert > Module.
Paste this code into the module: Sub InsertStudentPictures() Dim ws As Worksheet Dim lastRow As Long, r As Long Dim studentName As String, filePath As String Dim target As Range Dim pic As Shape Dim maxWidth As Double, maxHeight As Double If ThisWorkbook.Path = "" Then MsgBox "Save this workbook in the JPG folder first." Exit Sub End If Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row For r = 1 To lastRow studentName = Trim(CStr(ws.Cells(r, "B").Value)) If studentName <> "" Then filePath = ThisWorkbook.Path & Application.PathSeparator & studentName & ".jpg" Set target = ws.Cells(r, "A") maxWidth = target.Width - 4 maxHeight = target.Height - 4 If Dir(filePath) <> "" And maxWidth > 0 And maxHeight > 0 Then Set pic = ws.Shapes.AddPicture(filePath, msoFalse, msoTrue, target.Left, target.Top, -1, -1) pic.LockAspectRatio = msoTrue pic.Width = maxWidth If pic.Height > maxHeight Then pic.Height = maxHeight pic.Left = target.Left + (target.Width - pic.Width) / 2 pic.Top = target.Top + (target.Height - pic.Height) / 2 pic.Placement = xlMoveAndSize End If End If Next r End Sub
Return to the worksheet, select the sheet containing the names, and open Developer > Macros. Select InsertStudentPictures and click Run. Missing JPG files and cells too small for the padding are skipped.
Inspect column A and adjust row heights or column width if needed. The pictures are centered with padding so the cell borders remain visible.
Work with student spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with student lists and pictures; WPS does not fix Excel's VBA environment, and macro support can vary by version.
Frequently Asked Questions
Why did the macro skip a student?
Check that the JPG is in the workbook's folder and that its filename matches the text in column B without the .jpg extension. Also make sure the target cell is large enough to accommodate the picture and padding.
How do I put pictures in a different column?
In the VBA module, replace "A" in ws.Cells(r, "A") with the target column letter. The macro will still read names from column B.
Will the pictures move when I resize cells?
The macro sets each picture's Placement property to xlMoveAndSize, so it moves and resizes with its underlying cells. Later cell resizing may change the picture's proportions.
Can I run this macro in Excel for the web?
No. Open the .xlsm workbook in desktop Excel to use the VBA editor and run the macro. Enable macros only if you trust the workbook and its code.
