Code amendment needed for inserting images into a cell (VBA)

[kelly mort]

Sub Insert_Multiple_Images()

Set Image_Names = Range("B3:B8")
Image_Location = ThisWorkbook.Path & "\Images”
Image_Format = ".jpg”

Set Cell_Reference = Range("C3:C8")

For i = 1 To Image_Names.Rows.Count
    For j = 1 To Image_Names.Columns.Count
        Set Image = ActiveSheet.Pictures.Insert(Image_Location + "\" + Image_Names.Cells(i, j) + Image_Format)
        Image.Top = Cell_Reference.Cells(i, j).Top
        Image.Left = Cell_Reference.Cells(i, j).Left
        Image.ShapeRange.Height = 45
        Image.ShapeRange.Width = 20
    Next j
Next i

End Sub

On this line:

Image_Format = ".jpg”

I want it to be able to accept all image formats or extensions.

Then on these lines:

Image.Top = Cell_Reference.Cells(i, j).Top
Image.Left = Cell_Reference.Cells(i, j).Left

I want to get the images centered in the cells.

My row height is 30 at the moment.

Thanks in advance

 

[kelly mort]

Sub CenterImages()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
           With shp
               .Top = .TopLeftCell.Top + (.TopLeftCell.Height - .Height) / 2
               .Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2
           End With
    Next
End Sub

I found this code from stack overflow which is doing the center alignment for me.

Which means I am left with the dynamic file extension for the images

I tried

“.*”

But it didn’t work

 

[NdNoviceHlp]

Set Image = ActiveSheet.Pictures.Insert(Image_Location & "\" & Image_Names.Cells(i, j) & Image_Format)

HTH. Dave

 

[kelly mort]

Set Image = ActiveSheet.Pictures.Insert(Image_Location & "\" & Image_Names.Cells(i, j) & Image_Format)

HTH. Dave

With the above line, when I modified the image_format as :

Image_Format = ".*”

I get run time error 1004
Unable to get the Insert property of the picture class

 

[NdNoviceHlp]

See bullet #3 here... Using a wildcard to open an excel workbook
maybe..

Set Image = ActiveSheet.Pictures.Insert(Image_Location & "\" & Image_Names.Cells(i, j) & *)

Dave

 

[kelly mort]

@NdNoviceHlp
The above didn’t work either.
I don’t know why this line

Image_Format = ".*”

Is throwing that error message.
I have used that to load images into an image control on a userform before.