Using VBA to insert a picture into a cell

[mrtim2232]

Morning All,

I have been working on a program that will insert a picture into a cell but cant seem to get it working. Ideally it would insert the picture into the cell thats merged and size the picture to fit the cell. However because the name of the image I want could vary although the folder directory stays the same I need to use a cell value within the code and I think thats the bit the program gets stuck on the code is below and help would be appreciated.

Sub Macro1()
'
' Macro1 Macro
'


'
ActiveSheet.Pictures.Insert("C:\examplefile\test"& Range.("T3").Value & ".png").Select
End Sub

 

[Yongle]

There is an unneccessary character in your code

Instead of..

ActiveSheet.Pictures.Insert("C:\examplefile\test"& Range.("T3").Value & ".png").Select

Try..

ActiveSheet.Pictures.Insert("C:\examplefile\test" & Range("T3").Value & ".png").Select

 

[mrtim2232]

Thankyou!

It works great for getting the picture do you have any idea how I would get it to resize the image to fit the merged cell?

 

[Yongle]

Code below
- inserts a picture ("C:\examplefile\test" & Range("T3").Value & ".png") in the active cell
- picture is then resized to fit the cell (or nmerged cell)

Sub InsertPic()
    Dim PicPath As String, Pic As Picture, ImageCell As Range

    PicPath = "C:\examplefile\test" & Range("T3").Value & ".png"
    Set ImageCell = ActiveCell.MergeArea

    Set Pic = ActiveSheet.Pictures.Insert(PicPath)
    With Pic
        .ShapeRange.LockAspectRatio = msoFalse
        .Left = ImageCell.Left
        .Top = ImageCell.Top
        .Width = ImageCell.Width
        .Height = ImageCell.Height
    End With
End Sub

 

[mrtim2232]

Thankyou it works like a charm

 

[dlo1503]

I am trying to do nearly the exact same thing but without selecting the name of the file in the PicPath. Can this code open the folder the picture is stored in instead for an operator to select?

Thanks

 

[Yongle]

@dlo1503
Rather than hijacking this thread, please start a new thread of your own
In the first post include a copy of the code you are trying to modify and the explanation of what you want it to do
PM me a link to that thread and I will look at it when I get some time
thanks

 

[ashish128]

Code below
- inserts a picture ("C:\examplefile\test" & Range("T3").Value & ".png") in the active cell
- picture is then resized to fit the cell (or nmerged cell)

Sub InsertPic()
    Dim PicPath As String, Pic As Picture, ImageCell As Range

    PicPath = "C:\examplefile\test" & Range("T3").Value & ".png"
    Set ImageCell = ActiveCell.MergeArea

    Set Pic = ActiveSheet.Pictures.Insert(PicPath)
    With Pic
        .ShapeRange.LockAspectRatio = msoFalse
        .Left = ImageCell.Left
        .Top = ImageCell.Top
        .Width = ImageCell.Width
        .Height = ImageCell.Height
    End With
End Sub

Thank you for the guidance. It has helped me a lot in understanding on how to insert images and alter their display according to situational needs.