VBA Insert Picture Into Cell - Not working

[CainyUK]

Hi All,

I have this script that is working partially as intended - when importing the photos into the spreadsheet its posting them over the cell and not into the cell

In column B i have the names of the photos id like to import, column C is where id like to import the photos into

Sub InsertPictures()
    Const fPath = "C:\Madeupfilepath"
    Dim cel As Range, picPath As String
    
    For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
        On Error Resume Next
        picPath = fPath & "\" & cel.Value & ".jpg"
        If Not Dir(picPath, vbDirectory) = vbNullString Then
            With cel.Parent.Pictures.Insert(picPath)
                With .ShapeRange
                    .LockAspectRatio = msoFalse
                    .Width = 70
                    .Height = 70
                End With
                .Left = cel.Offset(0, 1).Left
                .Top = cel.Offset(0, 0).Top
            End With
        End If
    Next cel
End Sub

Ive attempted to alter the code to the following but im getting an error (insertpictureincell)

Sub InsertPictures()
    Const fPath = "C:\Madeupfilepath"
    Dim cel As Range, picPath As String
    
    For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
        On Error Resume Next
        picPath = fPath & "\" & cel.Value & ".jpg"
        If Not Dir(picPath, vbDirectory) = vbNullString Then
            With cel.Parent.Pictures.InsertPictureInCell(picPath)
                With .ShapeRange
                    .LockAspectRatio = msoFalse
                    .Width = 70
                    .Height = 70
                End With
                .Left = cel.Offset(0, 1).Left
                .Top = cel.Offset(0, 0).Top
            End With
        End If
    Next cel
End Sub

id also like the picture to resize to fit the cell upon import , but im abit unsure on how to do this

a solution would be hugely appreciated !!

 

[Georgiboy]

Welcome to the forum. perhaps the below will help:

Sub InsertPictures()
    Const fPath = "C:\Madeupfilepath"
    Dim cel As Range, picPath As String
    Dim pRng As Range
    
    For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
        picPath = fPath & "\" & cel.Value & ".jpg"
        If Not Dir(picPath, vbDirectory) = vbNullString Then
            With ActiveSheet.Pictures.Insert(picPath)
                Set pRng = cel.Offset(, 1)
                With .ShapeRange
                    .LockAspectRatio = msoFalse
                    .Left = pRng.Left
                    .Top = pRng.Top
                    .Width = pRng.Width
                    .Height = pRng.Height
                End With
            End With
        End If
    Next cel
End Sub

 

[CainyUK]

Hi Gorgi

Welcome to the forum. perhaps the below will help:

Sub InsertPictures()
    Const fPath = "C:\Madeupfilepath"
    Dim cel As Range, picPath As String
    Dim pRng As Range
   
    For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
        picPath = fPath & "\" & cel.Value & ".jpg"
        If Not Dir(picPath, vbDirectory) = vbNullString Then
            With ActiveSheet.Pictures.Insert(picPath)
                Set pRng = cel.Offset(, 1)
                With .ShapeRange
                    .LockAspectRatio = msoFalse
                    .Left = pRng.Left
                    .Top = pRng.Top
                    .Width = pRng.Width
                    .Height = pRng.Height
                End With
            End With
        End If
    Next cel
End Sub

thanks for this - whilst this is altering the size of the photo to be the same dimensions as the cell, sadly, its still placing the pictures over the cell and not in the cell :/

any suggestions?

 

[Georgiboy]

I'm not sure what you mean by over the cell?

Surely if the image is the size of the cell and aligned top and left then that is in the cell?

 

[CainyUK]

I'm not sure what you mean by over the cell?

Surely if the image is the size of the cell and aligned top and left then that is in the cell

So i need the picture to be embedded into the cell so it can be referenced by a formula from another sheet

ive attached 3 images to try and provide some context

"overview.jpg" - when inserting a picture you have two options, one to go over cells, the other to go in the cell

"overcell.jpg" - When the picture is over the cell, you get the resize dots

"incell.jpg" - When the photo is in the cell, you cannot resize and the picture can be pulled through to another sheet in my report

 

[Georgiboy]

I don't have access to Excel 365 at the moment so I don't have that option, have you tried recording a macro of you doing it manually and then compare the code to what you have?

If you have the pictures hosted online somewhere when you also have the option to use the formula '=IMAGE("Online Image Address")'
Insert images in cells with the IMAGE function in Excel

 

[RoryA]

The syntax to insert would be:

cel.InsertPictureInCell picPath

Note that this is a sub, and doesn't return a reference to the picture.

 

[sancarn]

@Dan_W Found that you need to Select the cell first:

Range("A1").select
'Must use ActiveCell directly:
ActiveCell.InsertPictureInCell "C:\Users\sancarn\Pictures\Screenshots\Screenshot 2023-03-05 015333.png"