How to insert a picture into Excel at a specified cell position with VBA

Question

I m adding   jpg  files to my Excel sheet with the code below     Add picture to excel xlApp Cells i  20  Select xlApp ActiveSheet Pictures Insert picPath  Select  Calgulate new picture size With xlApp Selection ShapeRange      LockAspectRatio   msoTrue      Width   75      Height   100 End With  Resize and make printable With xlApp Selection      Placement   1  xlMoveAndSize       Placement   2  xlMove       Placement   3  xlFreeFloating      PrintObject   True End With   I don t know what I am doing wrong but it doesn t get inserted into the right cell  so what should I do to put this picture into a specified cell in Excel

User · Answer

I have been working on a system that ran on a PC and Mac and was battling to find code that worked for inserting pictures on both PC and Mac. This worked for me so hopefully someone else can make use of it!

Note: the strPictureFilePath and strPictureFileName variables need to be set to valid PC and Mac paths Eg

For PC: strPictureFilePath = "E:\Dropbox\" and strPictureFileName = "TestImage.jpg" and with Mac: strPictureFilePath = "Macintosh HD:Dropbox:" and strPictureFileName = "TestImage.jpg"

Code as Follows:

    On Error GoTo ErrorOccured

    shtRecipeBrowser.Cells(intDestinationRecipeRowCount, 1).Select

    ActiveSheet.Pictures.Insert(Trim(strPictureFilePath & strPictureFileName)).Select

    Selection.ShapeRange.Left = shtRecipeBrowser.Cells(intDestinationRecipeRowCount, 1).Left
    Selection.ShapeRange.Top = shtRecipeBrowser.Cells(intDestinationRecipeRowCount, 1).Top + 10
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Height = 130

User · Answer

Try this   With xlApp ActiveSheet Pictures Insert PicPath      With  ShapeRange          LockAspectRatio   msoTrue          Width   75          Height   100     End With      Left   xlApp ActiveSheet Cells i  20  Left      Top   xlApp ActiveSheet Cells i  20  Top      Placement   1      PrintObject   True End With   It s better not to  select anything in Excel  it is usually never necessary and slows down your code

User · Answer

Firstly  of all I recommend that the pictures are in the same folder as the workbook  You need to enter some codes in the Worksheet Change procedure of the worksheet  For example  we can enter the following codes to add the image that with the same name as the value of cell in column A to the cell in column D  Private Sub Worksheet Change ByVal Target As Range  Dim pic As Picture If Intersect Target   A A   Is Nothing Then Exit Sub On Error GoTo son  For Each pic In ActiveSheet Pictures     If Not Application Intersect pic TopLeftCell  Range Target Offset 0  3  Address   Is Nothing Then         pic Delete     End If Next pic  ActiveSheet Pictures Insert ThisWorkbook Path  amp   quot   quot   amp  Target Value  amp   quot  jpg quot   Select Selection Top   Target Offset 0  2  Top Selection Left   Target Offset 0  3  Left Selection ShapeRange LockAspectRatio   msoFalse Selection ShapeRange Height   Target Offset 0  2  Height Selection ShapeRange Width   Target Offset 0  3  Width son   End Sub  With the codes above  the picture is sized according to the cell it is added to  Details and sample file here   Vba Insert image to cell

User · Answer

If it s simply about inserting and resizing a picture  try the code below   For the specific question you asked  the property TopLeftCell returns the range object related to the cell where the top left corner is parked  To place a new image at a specific place  I recommend creating an image at the  right  place and registering its top and left properties values of the dummy onto double variables   Insert your Pic assigned to a variable to easily change its name  The Shape Object will have that same name as the Picture Object   Sub Insert Pic From File PicPath as string  wsDestination as worksheet      Dim Pic As Picture  Shp as Shape     Set Pic   wsDestination Pictures Insert FilePath      Pic Name    myPicture       Strongly recommend using a FileSystemObject FileExists method to check if the path is good before executing the previous command     Set Shp   wsDestination Shapes  myPicture       With Shp          Height   100          Width   75          LockAspectRatio   msoTrue   Put this later so that changing height doesn t change width and vice-versa           Placement   1          Top   100          Left   100     End with End Sub   Good luck

User · Answer

I tested both  SWa and  Teamothy solution  I did not find the Pictures Insert Method in the Microsoft Documentations and feared some compatibility issues  So I guess  the older Shapes AddPicture Method should work on all versions  But it is slow   On Error Resume Next     first and faster method  in Office 2016        With ws Pictures Insert Filename  imageFileName  LinkToFile  msoTrue  SaveWithDocument  msoTrue          With  ShapeRange              LockAspectRatio   msoTrue              Width   destRange Width              height   destRange height  222         End With          Left   destRange Left          Top   destRange Top          Placement   1          PrintObject   True          Name   imageName     End With     second but slower method  in Office 2016     If Err Number  lt  gt  0 Then     Err Clear     Dim myPic As Shape     Set myPic   ws Shapes AddPicture Filename  imageFileName                LinkToFile  msoFalse  SaveWithDocument  msoTrue                Left  destRange Left  Top  destRange Top  Width  -1  height  destRange height       With myPic OLEFormat Object ShapeRange          LockAspectRatio   msoTrue          Width   destRange Width          height   destRange height  222     End With End If

User · Answer

Looking at posted answers I think this code would be also an alternative for someone  Nobody above used  Shapes AddPicture in their code  only  Pictures Insert    Dim myPic As Object Dim picpath As String  picpath    C  Users photo jpg   example photo path  Set myPic   ws Shapes AddPicture picpath  False  True  20  20  -1  -1   With myPic      Width   25      Height   25      Top   xlApp Cells i  20  Top  according to variables from correct answer      Left   xlApp Cells i  20  Left      LockAspectRatio   msoFalse End With   I m working in Excel 2013  Also realized that You need to fill all the parameters in  AddPicture  because of error  Argument not optional   Looking at this You may ask why I set Height and Width as -1  but that doesn t matter cause of those parameters are set underneath between With brackets   Hope it may be also useful for someone

[excel] How to insert a picture into Excel at a specified cell position with VBA

Examples related to excel

Examples related to image

Examples related to vba

Examples related to insert