{"id":12179,"date":"2013-06-30T13:45:36","date_gmt":"2013-06-30T13:45:36","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=3065"},"modified":"2017-09-04T13:45:16","modified_gmt":"2017-09-04T13:45:16","slug":"insert-a-picture-in-excel-using-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2013\/06\/insert-a-picture-in-excel-using-vba\/","title":{"rendered":"How to insert a picture in excel using VBA"},"content":{"rendered":"
Dear Friends, Insert Picture Using VBA Code<\/p><\/div><\/p>\n Basically, there are two methods to insert a picture in Excel Sheet Using .Pictures.Insert()<\/strong> method, you can insert a picture in Active sheet. Let see the Syntax of this method:<\/p>\n [highlight color=”yellow”]ActiveSheet.Pictures.Insert(‘Picture URL’)[\/highlight]<\/p>\n This function requires only one parameter – Full path of the picture to be inserted in Excel Sheet. This is a mandatory <\/strong>parameter here. <\/p>\n ActiveSheet.Pictures.Insert(“C:\\….\\myPic.jpg”)<\/p>\n Above statement will simply insert myPic.jpg<\/em><\/strong> picture in Active sheet in its original Size. Below code will set the height and width of the selected picture in worksheet which is inserted using VBA code:<\/p>\n Left and Top will be set without any issue. Challenge here is that you can NOT set AspectRatio flag <\/strong>of the picture while inserting it. (by above statement)<\/p>\n Therefore, be careful while resizing the picture while inserting it by using the above code<\/p>\n Here is the solution… Here is the code now…<\/strong><\/p>\n Here you can either set fixed Left and Top value where you want to place your picture. In this case no matter what is the height and width of the cell in the worksheet, your picture will be always placed at a specific location. But suppose if you want – your picture should always be placed at a specific row and column then you can set the left and top values as follows:<\/p>\n Now your selected picture will always be placed where Column A1 starts from left and Row 1 starts from top. It means even if you change height or width of the Range A1, your picture is always going to be in Range A1 only.<\/em><\/p>\n Warning!<\/strong> Therefore, this method is good only when you are going to use this excel sheet always in your own computer.<\/p><\/div>\n Using .Shapes.AddPicture()<\/strong> method, you can insert a picture in Active sheet. This method overcome the challenges of above method. This allows user to Embed the picture with the Excel Workbook itself. It means, even if you share the workbook to other computer… this picture will go with the document and you will be able to see it in other computer as well.<\/p>\n [highlight color=”yellow”].Shapes.AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height )[\/highlight]<\/p>\n Filename : (Mandatory)<\/strong> As the names suggests, this is the complete file path of the picture you want to embed to your Excel Sheet Following VBA code will Embed this picture with the Excel file and it will display in any computer you sent it. <\/p>\n Info ! <\/strong> For your practice I have created an Excel workbook which you can download and play around. VBA Code Insert Picture – Sample Workbook<\/p><\/div> Dear Friends, In this article, I am going to teach you a simple VBA code, which help you in inserting a picture in Excel Sheet. I will also discuss about difference between Inserting a picture in Excel and Embedding a picture in Excel Sheet using Excel VBA. It is based on request from one of […]<\/p>\n","protected":false},"author":45,"featured_media":14265,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1674,1682],"tags":[],"class_list":["post-12179","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-macro-basics","category-popular-articles"],"yoast_head":"\n
\nIn this article, I am going to teach you a simple VBA code, which help you in inserting a picture in Excel Sheet. I will also discuss about difference between Inserting a picture in Excel and Embedding a picture in Excel Sheet using Excel VBA.
\nIt is based on request from one of LEM reader who wants to know, <\/i>How to insert a picture in excel sheet using VBA code<\/i><\/strong> It is a very simple one liner code to insert a picture in Excel using vba code.
\n
\nMethod 1.<\/strong> ActiveSheet.Pictures.Insert
\nMethod 2.<\/strong> ActiveSheet.Shapes.AddPicture<\/p>\nVBA Code for Inserting Picture in Excel Sheet [Method 1]<\/h1>\n
Syntax of .Pictures.Insert<\/em> Method<\/h2>\n
For Example:<\/h1>\n
\nIf you want to resize and locate the picture according to you, then use the following statement to resize the image and place it where ever you want in the excel sheet.<\/p>\n1. VBA Code to re-size (height and width) the inserted picture<\/h2>\n
\r\n With ActiveSheet.Pictures.Insert("Picture full path")\r\n .Left = 110\r\n .Top = 220\r\n .Width = 123\r\n .Height = 134\r\n End With\r\n<\/code><\/pre>\n
Explanation and issues with above Code<\/h2>\n
\nLater, Width of the image will be set to 123 as specified – Height of the image will be automatically set to a respective height to the width – because AspectRatio<\/em><\/strong> of the image is by default set to True<\/strong>
\nSimilarly when control goes to the next statement then it will reset the height to 134 and since, aspect ratio is false, width will be adjusted to new respective value.<\/p>\nSo what is the solution?<\/h2>\n
\n1. first add the picture in its own size.
\n2. Store the name of this image (uniquely generated one) in a variable. So that you can refer this picture uniquely later on
\n3. Using this variable, select that Shape and set the aspect ratio to false<\/strong>
\n4. Then set the height and width of the picture.<\/p>\n\r\n Dim nameOfPicture as String\r\n With ActiveSheet.Pictures.Insert("Picture file full path")\r\n .Left = ActiveSheet.Range("photograph").Left + 2\r\n .Top = ActiveSheet.Range("photograph").Top + 2\r\n nameOfPicture= .Name\r\n End With\r\n ActiveSheet.Pictures(profile).Select\r\n With Selection.ShapeRange\r\n .LockAspectRatio = msoFalse\r\n .Width = 123\r\n .Height = 134\r\n End With\r\n<\/code><\/pre>\n
2. VBA Code to set the location of the inserted Picture<\/h2>\n
\r\n With ActiveSheet.Pictures.Insert(<path of your picture in local drive>)\r\n .Left = ActiveSheet.Range("A1").Left \r\n .Top = ActiveSheet.Range("A1").Top \r\n .Placement = 1\r\n End With\r\n<\/code><\/pre>\n
\nThis method, simply links the image in to your Excel Sheet. It means, after inserting a picture, using this method, if you send it to another computer, picture will not be displayed and an Error message be displayed.<\/p>\nVBA Code for Embedding Picture in Excel Sheet [Method 2]<\/h1>\n
Syntax of .Shapes.AddPicture<\/em> Method<\/h2>\n
Where:<\/h2>\n
\nLinkToFile : (Mandatory)<\/strong> MsoTriState- True or False – To set whether you want to create a link to the file?
\nSaveWithDocument : (Mandatory)<\/strong> MsoTriState – True or False – This is the flag which needs to be set to TRUE to embed the picture with Excel Sheet.
\nLeft : (Mandatory)<\/strong>The position of the upper-left corner of the picture with respect to the upper-left corner of the document.
\nTop : (Mandatory)<\/strong> The position (in points) of the upper-left corner of the picture with respect to the top of the document.
\nWidth : (Mandatory)<\/strong> The width of the picture you want to set. To keep the picture in its original width provide -1<\/em>
\nHeight : (Mandatory)<\/strong> The Height of the picture you want to set. To keep the picture in its original Height provide -1<\/em><\/p>\nExample:<\/h1>\n
\r\nActiveSheet.Shapes.AddPicture _\r\nFilename:="full path of your file with extension", _\r\nlinktofile:=msoFalse, savewithdocument:=msoCTrue, _\r\nLeft:=50, Top:=50, Width:=250, Height:=250\r\n<\/code><\/pre>\n
\nTherefore .Shapes.AddPicture Method can insert a picture with and without links just simply by passing some flags. <\/div>\n
\n
\n
\n
\n <\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"