Save image in excel using VBA

Question

I have a situation at work where people have to manually introduce pictures in a certain page of excel and resize it also manually. As a complete very beginner I've managed to find some VBA code to help introduce the picture by clicking a button and inserting it in a certain range of cells. The problem that I have is that I cannot figure out (after searching many posts) how to correctly introduce the function to save the image without making a link to it so others can see the report without getting an error that the picture doesn't exist.

Can you kindly help me and complete where the function should be introduced?

Private Sub CommandButton3_Click()
Dim strFileName As String
Dim objPic As Picture
Dim rngDest As Range
strFileName = Application.GetOpenFilename( _
    FileFilter:="Images (*.jpg;*.gif;*.png),*.jpg;*.gif;*.png", _
    Title:="Please select an image...")
If strFileName = "False" Then Exit Sub
Set rngDest = Me.Range("B24:C26")
Set objPic = Me.Pictures.Insert(strFileName)
With objPic
    .ShapeRange.LockAspectRatio = msoFalse
    .Left = rngDest.Left
    .Top = rngDest.Top
    .Width = rngDest.Width
    .Height = rngDest.Height
End With
End Sub

Thanks in advance!

Sorry, I've tried this and it gives me an error saying "run-time error '448', named argument not found. — Liviu Andrei
– Liviu Andrei, Commented Mar 27, 2020 at 6:26
Set objPic = Me.Pictures.Insert(strFileName) is correct. It should insert the image without making it a link. You can also use Shapes.AddPicture method (Excel). Something like Dim objPic As Object and then in the next line Set objPic = ActiveSheet.Shapes.AddPicture(strFileName, False, True, 10, 10, -1, -1) — Siddharth Rout
– Siddharth Rout, Commented Mar 27, 2020 at 6:53
@SiddharthRout Thank you very much. I've made the test with your suggestion two computers and it works ! I wish you a really good day :) For others to know ,I've deleted this line since it was giving an error and added the activesheet as Siddharth said. code ShapeRange.LockAspectRatio = msoFalse — Liviu Andrei
– Liviu Andrei, Commented Mar 27, 2020 at 7:23
If you are running the code from the sheet module then you can continue using Me but if you are running this code from a userform then you should declare your worksheet object and use that. For example Dim ws as Worksheet and then Set ws = Sheet1 and finally Set objPic = ws.Shapes.AddPicture.... — Siddharth Rout
– Siddharth Rout, Commented Mar 27, 2020 at 7:59

Tim Williams · Accepted Answer · 2020-03-27 07:00:09Z

3

Try this:

Private Sub CommandButton3_Click()
    Dim strFileName As String
    Dim objPic As Shape '<<<
    Dim rngDest As Range
    strFileName = Application.GetOpenFilename( _
        FileFilter:="Images (*.jpg;*.gif;*.png),*.jpg;*.gif;*.png", _
        Title:="Please select an image...")
    If strFileName = "False" Then Exit Sub
    Set rngDest = Me.Range("B24:C26")

     Set objPic = Me.Shapes.AddPicture(Filename:=strFileName, _
                                       linktofile:=msoFalse, _
                                       savewithdocument:=msoCTrue, _
                                       Left:=rngDest.Left, Top:=rngDest.Top, _
                                       Width:=rngDest.Width, Height:=rngDest.Height)

End Sub

edited Mar 27, 2020 at 7:00

answered Mar 27, 2020 at 6:53

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liviu Andrei Over a year ago

thank you also very much for your help, I made the test like Siddharth said and it worked. Hopefully in the future someone will see this codes and it will help them.

Collectives™ on Stack Overflow

Save image in excel using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related