Automating "Place in Cell" for Pictures Inserted in Excel Using VBA or Other Tools

[latarshac]

Hello Excel experts!

I’m currently working on a project that involves inserting images into Excel sheets. I use Kutools' "Insert Pictures from Path (URL)" feature to quickly add images from URLs or file paths into my workbook. While this feature works great, it inserts all images as "Place Over Cell," requiring me to manually click each image, then go to the menu and select "Place in Cell."

The Problem:

This manual process is extremely time-consuming, especially when dealing with hundreds of images. I’d like to automate the process so that:​

1. The script checks if the URL column (D) is not blank.
2. For each corresponding image inserted in column (B), it ensures the image is placed in the cell rather than over it.

Desired Outcome:

All images should fit within their respective cells (B), and adjust automatically when rows or columns are resized.​

Current Setup:

Kutools to insert pictures from URLs in column (B)​

URL references are stored in column (D)​

What I’ve Tried:

I looked into some VBA solutions but haven’t found a clear method to programmatically replicate the "Place in Cell" option for pictures.​

Request:

Does anyone know a VBA script or another tool/approach to automat~e this task?​

Any resources or examples would be greatly appreciated!​

Thanks in advance for your help!
Tarsha

 

[jolivanes]

The so called empty cells need to be genuinely empty

Sub Maybe()
Dim c As Range
    For Each c In Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row)
        If Len(c) <> 0 Then
            ActiveSheet.Shapes.AddPicture c.Value, False, True, c.Offset(, -2).Left, c.Offset(, -2).Top, c.Offset(, -2).Width, c.Offset(, -2).Height
        End If
    Next c
End Sub

How to change size when changing column width and row height after the pictures are brought in needs to be added.

 

[Artik]

If you want to “fix” pictures that Kutools couldn't insert into cells, then use Macro1.
If you want to insert pictures in bulk based on the data in column D, then use Macro2.

Sub Macro1()
    Dim Pic As Picture
    
    Application.ScreenUpdating = False
    
    For Each Pic In ActiveSheet.Pictures
        If Pic.TopLeftCell.Column = 2 Then
            Pic.Select
            Application.CommandBars.ExecuteMso "PicturePlaceInCell"
        End If
    Next Pic
    
End Sub


Sub Macro2()
    Dim rng As Range
    Dim rngScope As Range
    
    Application.ScreenUpdating = False
    
    Set rngScope = Range("D2", Cells(Rows.Count, "D").End(xlUp))
    
    For Each rng In rngScope.Cells
        If Len(rng.Value) > 0 Then
            If LCase(rng.Value) Like "http*://*.jpg" Then
                
                If rng.Offset(, -2).HasRichDataType = True Then
                    rng.Offset(, -2).Value = Empty
                End If
                
                rng.Offset(, -2).InsertPictureInCell rng.Value
            End If
        End If
    Next rng
    
End Sub

Artik