Default picture size with Shapes.AddPicture

I have often resisted using the Shapes.AddPicture method, sticking with the deprecated Pictures.Insert method, because with the former you have to specify the dimensions of the picture (i.e. both Height and Width) as well as the location and usually I don’t know them (and can’t be bothered with the code to get them). Unfortunately the Pictures.Insert method gives you no control over whether the image is linked or embedded.

 

However, this morning I realised that you can avoid this problem by simply passing -1 as the value for both Height and Width! (a real “why the hell didn’t I think of that before?” moment)

 

So the code is simply:

ActiveSheet.Shapes.AddPicture Filename:="path to pic", linktofile:=msoFalse, _

savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1

18 thoughts on “Default picture size with Shapes.AddPicture

  1. Rob says:

    Thank you, thank you, thank you!

    Hey, did I say thank you, cause if I didn’t, thank you.

    This save me hours of grief, since I haven’t found any other way to do this easily. Nobody else seems to have published this workaround, and there’s no way I would have know to even try this.

    So, thanks again.

  2. Greg says:

    I am adding 50 pictures to an excel sheet using ‘shape.addpicture’ method but sometimes they come out blurry. I have switched to insert method, ie.

    With ActiveSheet.Pictures.Insert(filepig(picarray))
    where filepig is the filename. However, these are linked to the picture, not embedded. How can I fix this!! I cannot use either method! Thanks for helping this VB neophyte.

    Greg

    • Rory Archibald says:

      What code are you using exactly when they come out blurry, and is there any common factor with the pictures this happens to?

  3. Serena says:

    I have the similar problem as Grey earlier said but I use “command button” in excel.

    With use of the following, these are just linked to the picture, not embedded. SPicture is setting as the picture inserted by clicking command button.

    Set pic = ActiveSheet.Pictures.Insert(sPicture)

    But when I use either of the following, it comes out as error and asking for debug!!
    Set pic = ActiveSheet.Shapes.AddPicture(sPicture)
    or
    Set pic = Application.ActiveSheet.Shapes.AddPicture(sPicture)

    How can I insert and embedded the pictures into the excel file with commanded button??!! I cannot use either method! Thanks a lot for helping this!!

    • Rory Archibald says:

      None of the arguments for AddPicture (there are 7 in all) is optional – you have to supply them all, as in the example I provided.

  4. Brendan says:

    Thank you for this. I had completed my hack before finally finding this. The hack way I did it was to use both methods to insert the same image, then took the dimensions from the picture i inserted with the pictures.insert method to then properly scale the embedded image from shapes.addpicture. This makes my code a lot cleaner and faster since I don’t have to fetch the file(which is coming from a URL) twice.

      • Tom says:

        Hi Rory : )
        Thank you for your help!
        Can you tell me please
        how can you insert the images in the middle of the cell horizontally and vertically at the same time?
        I mean now the reference point of the insertation is the upper-left corner of the cell. How can you make the reference point to be in the middle of the cell?
        Best

        • Rory Archibald says:

          Do you want the top left corner of the picture at the centre of the cell, or do you want the picture centred over the cell?

          • Rory Archibald says:

            You can do something like this:

                Dim rgTarget As Range
                Set rgTarget = Range("d10")
                With ActiveSheet.Shapes.AddPicture(fileName:="E:\blah.gif", linktofile:=msoFalse, _
                                                    savewithdocument:=msoCTrue, left:=rgTarget.left, _
                                                    top:=rgTarget.top, Width:=-1, Height:=-1)
                    .IncrementLeft (rgTarget.Width / 2) - (.Width / 2)
                    .IncrementTop (rgTarget.Height / 2) - (.Height / 2)
                End With
          • Tom says:

            I don’t know how to use your code above, because I have a certain code, and I don’t know how to collect it with your code : ( Can you help me please?

            This is the code I use for inserting images based on cell values:

            Sub Button2_Click()
            Call InsertPicture
            End Sub
            Sub InsertPicture()

            Dim xPath As String
            Dim xLastRow As Long
            Dim Rng As Range
            Dim WorkRng As Range

            Dim p As Object
            On Error Resume Next
            xTitleId = “forExcel”
            Set WorkRng = Application.Selection
            Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)
            Application.ScreenUpdating = False

            xPath = “C:\Users\Desktop\”

            If Right(xPath, 1) “” Then xPath = xPath & “”

            xLastRow = Cells(Rows.Count, “A”).End(xlUp).Row
            For Each Rng In WorkRng
            If Rng.Value “” Then

            If Dir(xPath & Rng.Value & “.jpg”) “” Then
            ‘If Dir(xPath & Rng.Value & “.jpg”) “” Then

            Set p = ActiveSheet.Shapes.AddPicture(Filename:=xPath & Rng.Value & “.jpg”, _
            linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1)

            p.Select
            With Selection.ShapeRange
            .LockAspectRatio = msoTrue
            .Left = Rng.Left
            .Top = Rng.Top
            .Width = Rng.Width
            .Height = Rng.Height
            End With
            Rng.ClearContents
            Else
            Rng.Value = “N/A”
            End If
            End If
            Next

            Application.DisplayAlerts = False
            ‘Application.Save
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
            End Sub

          • Tom says:

            Hi Rory! : )
            Don’t you know how to make it?
            I’m not a VBA master, but I have the code below and I don’t know how to make it work with the you you’ve offered. Can you help me please?
            Thank you very much!

  5. Pingback: Allow users to browse for and insert picture

Leave a Reply

Your email address will not be published. Required fields are marked *