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 LEM reader who wants to know, How to insert a picture in excel sheet using VBA code It is a very simple one liner code to insert a picture in Excel using vba code.
Insert Picture Using VBA Code
Basically, there are two methods to insert a picture in Excel Sheet
Method 1. ActiveSheet.Pictures.Insert
Method 2. ActiveSheet.Shapes.AddPicture
VBA Code for Inserting Picture in Excel Sheet [Method 1]
Using .Pictures.Insert() method, you can insert a picture in Active sheet. Let see the Syntax of this method:
Syntax of .Pictures.Insert Method
[highlight color=”yellow”]ActiveSheet.Pictures.Insert(‘Picture URL’)[/highlight]
This function requires only one parameter – Full path of the picture to be inserted in Excel Sheet. This is a mandatory parameter here.
For Example:
ActiveSheet.Pictures.Insert(“C:\….\myPic.jpg”)
Above statement will simply insert myPic.jpg picture in Active sheet in its original Size.
If 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.
1. VBA Code to re-size (height and width) the inserted picture
Below code will set the height and width of the selected picture in worksheet which is inserted using VBA code:
With ActiveSheet.Pictures.Insert("Picture full path")
.Left = 110
.Top = 220
.Width = 123
.Height = 134
End With
Explanation and issues with above Code
Left and Top will be set without any issue.
Later, 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 of the image is by default set to True
Similarly 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.
Challenge here is that you can NOT set AspectRatio flag of the picture while inserting it. (by above statement)
Therefore, be careful while resizing the picture while inserting it by using the above code
So what is the solution?
Here is the solution…
1. first add the picture in its own size.
2. Store the name of this image (uniquely generated one) in a variable. So that you can refer this picture uniquely later on
3. Using this variable, select that Shape and set the aspect ratio to false
4. Then set the height and width of the picture.
Here is the code now…
Dim nameOfPicture as String
With ActiveSheet.Pictures.Insert("Picture file full path")
.Left = ActiveSheet.Range("photograph").Left + 2
.Top = ActiveSheet.Range("photograph").Top + 2
nameOfPicture= .Name
End With
ActiveSheet.Pictures(profile).Select
With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Width = 123
.Height = 134
End With
2. VBA Code to set the location of the inserted Picture
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:
With ActiveSheet.Pictures.Insert(<path of your picture in local drive>)
.Left = ActiveSheet.Range("A1").Left
.Top = ActiveSheet.Range("A1").Top
.Placement = 1
End With
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.
Warning!
This 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.
Therefore, this method is good only when you are going to use this excel sheet always in your own computer.
VBA Code for Embedding Picture in Excel Sheet [Method 2]
Using .Shapes.AddPicture() 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.
Syntax of .Shapes.AddPicture Method
[highlight color=”yellow”].Shapes.AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height )[/highlight]
Where:
Filename : (Mandatory) As the names suggests, this is the complete file path of the picture you want to embed to your Excel Sheet
LinkToFile : (Mandatory) MsoTriState- True or False – To set whether you want to create a link to the file?
SaveWithDocument : (Mandatory) MsoTriState – True or False – This is the flag which needs to be set to TRUE to embed the picture with Excel Sheet.
Left : (Mandatory)The position of the upper-left corner of the picture with respect to the upper-left corner of the document.
Top : (Mandatory) The position (in points) of the upper-left corner of the picture with respect to the top of the document.
Width : (Mandatory) The width of the picture you want to set. To keep the picture in its original width provide -1
Height : (Mandatory) The Height of the picture you want to set. To keep the picture in its original Height provide -1
Example:
Following VBA code will Embed this picture with the Excel file and it will display in any computer you sent it.
ActiveSheet.Shapes.AddPicture _
Filename:="full path of your file with extension", _
linktofile:=msoFalse, savewithdocument:=msoCTrue, _
Left:=50, Top:=50, Width:=250, Height:=250
Info !
Therefore .Shapes.AddPicture Method can insert a picture with and without links just simply by passing some flags.
For your practice I have created an Excel workbook which you can download and play around.
VBA Code Insert Picture – Sample Workbook
Hi, I wrote something like here using VBA code to insert picture by command button. I found the some problem as here is that the inserted picture cannot be read by the different computers other than the one used to insert the picture.
I downloaded your file, try and find this program here got the same problem as I had. Can you revise this code so as to ensure the inserted picture can be read by different computer?? Many thanks.
Hi Serena,
Here is the solution
(Sorry for the too late response..your question was somewhere lost )
[code language=”vb”]
ActiveSheet.Shapes.AddPicture Filename:=fd.SelectedItems(1), linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=50, Top:=50, Width:=250, Height:=250
[/code]
Sub AddOlEObject()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
Sheets(“Object”).Activate
Folderpath = “C:\Users\javed\Desktop\New Project\Pictures”
Set fso = CreateObject(“Scripting.FileSystemObject”)
NoOfFiles = fso.GetFolder(Folderpath).Files.Count
Set listfiles = fso.GetFolder(Folderpath).Files
For Each fls In listfiles
strCompFilePath = Folderpath & “\” & Trim(fls.Name)
If strCompFilePath “” Then
If (InStr(1, strCompFilePath, “jpg”, vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, “jpeg”, vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, “png”, vbTextCompare) > 1) Then
counter = counter + 1
Sheets(“Object”).Range(“A” & counter).Value = fls.Name
Sheets(“Object”).Range(“B” & counter).ColumnWidth = 25
Sheets(“Object”).Range(“B” & counter).RowHeight = 100
Sheets(“Object”).Range(“B” & counter).Activate
Call insert(strCompFilePath, counter)
Sheets(“Object”).Activate
End If
End If
Next
ActiveSheet.Range(“E3”).Select
End Sub
Function insert(PicPath, counter)
‘MsgBox PicPath
With ActiveSheet.Pictures.insert(PicPath)
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 50
.Height = 70
End With
.Left = ActiveSheet.Range(“B” & counter).Left
.Top = ActiveSheet.Range(“B” & counter).Top
.Placement = 1
.PrintObject = True
End With
End Function
Hi Vish can you help me with this code.
I am unable to send the file to another PC it is showing error on picture column
Heloo,
I just wanted to know how can i retrive a image from desktop without selecting a range
for example:
i have created a macro and added a button in ribbon, so what i wanted is that when i click on that macro it should give a pop-up asking a name, after inserting name it should pop-up image.
can anyone please help.
Thanks,
karteek
i want to put a loop in this macro for inserting the images
i want to insert image in coloumn A & name of the image is from coloumn B
how to do this
‘as long as all of you cells have a value you could try this….
‘s is a range of cells (I always use column “a” as a base)
‘xRow is the row number of the active row on the sheet
dim s as range
dim xRow as long
set s= activesheet.range(“a:a”)
xRow = 1 ‘this can be the row before the data starts but must have
‘some sort of value in, as the code stops on an empty cell
do while s.cells(xrow,”a”) “” ‘stop whe you get to a blank cell
xrow=xrow+1 ‘ move one row down
if s.cells(xrow,”a”) = “” then exit do ‘ if it’s blank stop now
‘enter the results in column “b”
‘ this would be
s.cells(xrow,”b”) = ‘CALL THE MODULE WITH THE CODE ABOVE and
‘get the image to put into that cell reference
loop
Hi..i can’t find the template that u mention..
Hello Vish, first to thank you for the code and example. I am truly thankful for this.
In your example if you click Cancel button on import image, code throw Run-time error 5: Invalid procedure or argument.
i have tried this code to insert image , and the images are been insert, but i have many images may be 21 images in one page and after 3 or fourth page the inserted image range is not as given range in macro
please help , the image is shown above the range and it keeps on shifting its place as per the page increase , i am using excel 2007
If you share your code which you are using, I may be able to help you on this.
Hi Vish,
I am trying to insert images via excel macro.
The code works well in my pc as desired however when the output excel is transferred to another pc it says link ot image not found and image is not displayed
I have mailed you my code please have a look and suggest way forward
Regards
JD
Hi Jaydeep,
I see the issue. ActiveSheet.Picture.insert – simply link that image to your excel sheet. When you move your file to another computer then this error appears.
Thanks for pointing this out. I will add a note section in my post to help other users like you.
Here is the solution
[code language=”vb”]
ActiveSheet.Shapes.AddPicture Filename:="full path of your file with extension", linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=50, Top:=50, Width:=250, Height:=250
[/code]
thanks Vish this worked
can you provide the complete code for inserting all the pictures from a folder
Hi,
In order to embed a picture with no links, the code “ActiveSheet.Shapes.AddPicture” & “linktofile:=msoFalse” works perfectly under Windows XP.
However, under Windows 10: image blank…
Any good idea?
Thks, Gerard
Dear Gerard,
Thanks for contacting. If you can answer following questions.. then I may be able to help you:
1. What is excel version are you using in XP and Windows 10?
2. What is the type of image are you embedding like jpeg, png .. etc?
3. When you say it is appearing blank.. Does it mean you are able to see a while placeholder? or nothing at all?
can you help me update this code so i dont get error message upon hitting cancel when trying to select an image?
Sub InsertPicUsingShapeAddPictureFunction()
Dim profile As String
On Error GoTo 0
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add “Picture Files”, “*.bmp;*.jpg;*.gif;*.png”
.ButtonName = “Select”
.AllowMultiSelect = False
.Title = “Choose Photo”
.InitialView = msoFileDialogViewDetails
.Show
End With
ActiveSheet.Range(“AS3”).Select
ActiveSheet.Shapes.AddPicture Filename:=fd.SelectedItems(1), _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoCTrue, _
Left:=ActiveSheet.Range(“AS3”).Left + 2, _
Top:=ActiveSheet.Range(“AS3”).Top + 2, _
Width:=27.57, _
Height:=71.25
End Sub
Hello,
How do you lock the aspect ratio in the second method? I would like to make the image bigger and keep its original ratio.
Thank you, this was very helpful!
Thanks for your good work.
I m very nee to VBA.
Please help for one problem..
I have sheet 1 for 40 students photo album roll no wise.
I want display it automatically in sheet 2 in B4 cell according to roll no mentioned in sheet 2 in A4 cell.
When I change roll no , photo changed automatically according to roll no.
Please help.
Hi Thanks!
I would recommend not to use macro for this task, rather you can use formula method which can be done easily. Here is one reference link … https://www.extendoffice.com/documents/excel/4025-excel-dynamic-image-in-cell.html
Hello,
I am totally agree that we can use the above link but the problem with this formula is that your images will become WMF images not an png image so the file size is getting increased.
could you please help me on that
i get a debug on filters.clear??
Sub InsertPicUsingShapeAddPictureFunction()
Dim profile As String
On Error GoTo 0
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add “Picture Files”, “*.bmp;*.jpg;*.gif;*.png”
.ButtonName = “Select”
.AllowMultiSelect = False
.Title = “Choose Photo”
.InitialView = msoFileDialogViewDetails
.Show
End With
ActiveSheet.Range(“E25”).Select
ActiveSheet.Shapes.AddPicture Filename:=fd.SelectedItems(1), _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoCTrue, _
Left:=ActiveSheet.Range(“photograph2”).Left + 2, _
Top:=ActiveSheet.Range(“photograph2”).Top + 2, _
Width:=123, _
Height:=134
End Sub
Thanks Graham for asking this question here.
When you say. I get a debug – do you mean that you are not even able to see the FileDialog file picker? ?
Can you also share which version of Excel you are using?
By the way…looking at your code, I do not see any problem with .Filter.clear statement. It should work without any error.
need help.
i cant rename the picture so i can’t select the picture to format it.
Hi Vish, Thank you for the post. I was able to work with method 2 adding files.
I was looking for an example to save a file as well.
I load data to Excel from a database after clearing the active sheet..
I did save the file to c:\temp and pull it from there.
If I move to to another system, I would rather save it to the the temp folder.
Although, I could save it to another worksheet, too.
Thanks.
so this is about using macros but i want to assign macros to import pictures i guess temporarily into excel sheet using 2010 on windows 10.
need to assign images from file to show when a button is pressed and then different image when a different button is pressed? i hope this makes sense
Hi Vish,
First of all : thank you! .
With InsertPicUsingShapeAddPictureFunction(), I turned .AllowMultiSelect from False to True, and add a loop for/next to get the selected images : All is worrking fine. But I have 2 questions :
1 : how to give a specific name to each image in the loop?
2 : how to put each one under the precedent into the same column ?
Thank’s again.
Henri
Hi Guys,
This has been very useful. Thank you. However, I am still very new this and trying to wrap my head around it. I have a few queries and I was hoping i could get assistance on this.
I would like to use VBA to extract an image from a website based on a singular perimeter. For example, If cell L16 is “Apple”, I would like the code to run a search against a particular website to extract that image. The search perimeters should be asked on whatever is in the L column
Appreciate if i could get advice on how to insert this code.
Many thanks in advance
I don’t know how to insert a picture in excel using VBA. I believe that this article will definitely help me accomplish it. Thanks for sharing this article. I will follow what you said here. This article is really helpful.
Say we have a product line with different options, can we build a macro with user input to build the product with the options wanted?
Yes you can 🙂
Sub Pic_Emb()
On Error Resume Next
Dim directory As String
For i = 2 To Cells(Cells.Rows.Count, 5).End(3).Row
directory = Range(“E” & i).Hyperlinks(1).Address
Set resim = ActiveSheet.Shapes.AddPicture(directory, True, True, Range(“F” & i).Left, Range(“F” & i).Top, 134, Range(“F” & i).Height)
resim.Name = Range(“B” & i)
Next
End Sub
I try to embed pictures from Hyperlinks but it takes only first and last row. And it names first picture as 4th row. I’m about to get crazy. Please help me.
How to insert a picture on the ActiveSheet, but this time without adding any format, just the picture name. Meaning that the picture is always going to have the same name, but the format may change.
You should take part in a contest for one of the best websites
online. I will highly recommend this web site!
Hi ,
My query is different , I will like to use option button instead of command button to toggle between pictures at a defined location , ie , when i press Option-1 it shows the picture of apple , when i press Option -2 it shows the picture of orange & hides the apple image “at the same defined location” , Option -3 it shows the picture of Mango & hides other image . ….so on ….