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?

Answer 1

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.

Answer 2

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 :)