Insert picture in excel macro which takes the file name refrence [Solved/Closed]
V Kanagu
Moderator Last seen
December 13, 2018
- Mar 14, 2017 at 12:18 PM
- Posts
- 1
- Registration date
- Friday August 23, 2013
- Last seen
- August 23, 2013
- Posts
- 2440
- Registration date
- Sunday September 12, 2010
- Status
Related:
- Insert picture in excel cell automatically
- Excel vba insert picture from folder - Best answers
- Excel vba insert picture based on cell value - Best answers
- Insert picture into excel cell macro - How-To - Excel
- Insert picture in excel cell with vba ✓ - Forum - Excel
- Insert filename in excel cell - How-To - Excel
- Macro to insert picture in excel - How-To - Excel
- How to insert date in excel sheet automatically - How-To - Excel
4 replies
Best answer
- Posts
- 2440
- Registration date
- Sunday September 12, 2010
- Status
Hi V Kanagu,
Here is your code:
To try it out, right-click on your sheets tab, select View code.
Paste the code in the big white field.
Now change the value in D5 and see if the picture shows in your merged range.
Best regards,
Trowa
Here is your code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5")) Is Nothing Then Exit Sub
Dim myPict As Picture
With Range("I7:J12")
Set myPict = Range("I7:J12").Parent.Pictures.Insert("F:\" & Target.Value & ".jpg")
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With
End Sub
To try it out, right-click on your sheets tab, select View code.
Paste the code in the big white field.
Now change the value in D5 and see if the picture shows in your merged range.
Best regards,
Trowa
A few words of thanks would be greatly appreciated. Add comment
2948 users have said thank you to us this month
- Posts
- 2440
- Registration date
- Sunday September 12, 2010
- Status
Hi Cakmak,
Try the code below.
The picture height is determined by the row height and not the other way around, because screen size is also a factor.
Here is the code:
Best regards,
Trowa
Try the code below.
The picture height is determined by the row height and not the other way around, because screen size is also a factor.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Dim myPict As Picture
With Cells(Target.Row, "D")
Set myPict = Cells(Target.Row, "D").Parent.Pictures.Insert("D:\images\" & Target.Value & ".jpg")
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With
End Sub
Best regards,
Trowa
Dear Trowa,
Here is the code I am using for my excel macro with a button.
While using it I have seen that I need some more modification which lets me to :
1. Keep aspect ratio of pictures
2. Move and size with cells.
for the images.
Is it possible to do that?
Thanks.
Here is the code I am using for my excel macro with a button.
While using it I have seen that I need some more modification which lets me to :
1. Keep aspect ratio of pictures
2. Move and size with cells.
for the images.
Is it possible to do that?
Thanks.
Private Sub CommandButton1_Click()
Dim pictureNameColumn As String 'column where picture name is found
Dim picturePasteColumn As String 'column where picture is to be pasted
Dim pictureName As String 'picture name
Dim lastPictureRow As Long 'last row in use where picture names are
Dim pictureRow As Long 'current picture row to be processed
Dim pathForPicture As String 'path of pictures
pictureNameColumn = "A"
picturePasteColumn = "H"
pictureRow = 5 'starts from this row
'error handler
On Error GoTo Err_Handler
'find row of the last cell in use in the column where picture names are
lastPictureRow = Cells(Rows.Count, pictureNameColumn).End(xlUp).Row
'stop screen updates while macro is running
Application.ScreenUpdating = False
pathForPicture = "C:\images\"
'loop till last row
Do While (pictureRow <= lastPictureRow)
pictureName = Cells(pictureRow, "A") 'This is the picture name
'if picture name is not blank then
If (pictureName <> vbNullString) Then
'check if pic is present
If (Dir(pathForPicture & pictureName & ".jpg") <> vbNullString) Then
Cells(pictureRow, picturePasteColumn).Select 'This is where picture will be inserted
ActiveSheet.Pictures.Insert(pathForPicture & pictureName & ".jpg").Select 'Path to where pictures are stored
With Selection
.Left = Cells(pictureRow, picturePasteColumn).Left
.Top = Cells(pictureRow, picturePasteColumn).Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 130#
.ShapeRange.Rotation = 0#
End With
Else
'picture name was there, but no such picture
Cells(pictureRow, picturePasteColumn) = "No Picture Found"
End If
Else
'picture name cell was blank
End If
'increment row count
pictureRow = pictureRow + 1
Loop
Exit_Sub:
Range("A10").Select
Application.ScreenUpdating = True
Exit Sub
Err_Handler:
MsgBox "Error encountered. " & Err.Description, vbCritical, "Error"
GoTo Exit_Sub
End Sub
- Posts
- 2440
- Registration date
- Sunday September 12, 2010
- Status
Hi Cakmak,
1.
To keep aspect ratio change:
.ShapeRange.LockAspectRatio = msoFalse
into:
.ShapeRange.LockAspectRatio = msoTrue
2.
Try this, but know that this overwrites the aspect ratio:
Best regards,
Trowa
1.
To keep aspect ratio change:
.ShapeRange.LockAspectRatio = msoFalse
into:
.ShapeRange.LockAspectRatio = msoTrue
2.
Try this, but know that this overwrites the aspect ratio:
With Selection
.Left = Cells(pictureRow, picturePasteColumn).Left
.Top = Cells(pictureRow, picturePasteColumn).Top
.ShapeRange.LockAspectRatio = msoTrue 'Point 1
.ShapeRange.Height = 10#
.ShapeRange.Width = 130#
.ShapeRange.Rotation = 0#
.Placement = xlMoveAndSize 'Point 2
End With
Best regards,
Trowa
I have my huge files and below is a sample.
I need to add photos for everycode in coloumn "A" to the same row coloumn "E"
and I want to run a macro to add photos just after I filled up the codes coloumn and other necessary information.
Lets say image files are found in "D:\images\" folder with the same names mentioned in coloumn "A"
Besides when I add the photos I want the images to be 4,6 cm height plus pictures should be placed in the center of the cell and I need the cell height to be arranged automatically also which will fit to pictures height.
Anybody can help? for this VBAless guy? :)
Thanks.
I need to add photos for everycode in coloumn "A" to the same row coloumn "E"
and I want to run a macro to add photos just after I filled up the codes coloumn and other necessary information.
Lets say image files are found in "D:\images\" folder with the same names mentioned in coloumn "A"
Besides when I add the photos I want the images to be 4,6 cm height plus pictures should be placed in the center of the cell and I need the cell height to be arranged automatically also which will fit to pictures height.
Anybody can help? for this VBAless guy? :)
Thanks.
- Posts
- 2440
- Registration date
- Sunday September 12, 2010
- Status
Hi Sachin,
For that you will need to add some ElseIf's.
I added two of them in the code of Cakmak. Look for the green text to see the start and end of these ElseIf blocks, so you could add more if needed.
Here is the adjusted code:
Best regards,
Trowa
For that you will need to add some ElseIf's.
I added two of them in the code of Cakmak. Look for the green text to see the start and end of these ElseIf blocks, so you could add more if needed.
Here is the adjusted code:
Private Sub CommandButton1_Click()
Dim pictureNameColumn As String 'column where picture name is found
Dim picturePasteColumn As String 'column where picture is to be pasted
Dim pictureName As String 'picture name
Dim lastPictureRow As Long 'last row in use where picture names are
Dim pictureRow As Long 'current picture row to be processed
Dim pathForPicture As String 'path of pictures
pictureNameColumn = "A"
picturePasteColumn = "H"
pictureRow = 5 'starts from this row
'error handler
On Error GoTo Err_Handler
'find row of the last cell in use in the column where picture names are
lastPictureRow = Cells(Rows.Count, pictureNameColumn).End(xlUp).Row
'stop screen updates while macro is running
Application.ScreenUpdating = False
pathForPicture = "C:\images\"
'loop till last row
Do While (pictureRow <= lastPictureRow)
pictureName = Cells(pictureRow, "A") 'This is the picture name
'if picture name is not blank then
If (pictureName <> vbNullString) Then
'check if pic is present
'Start If block with .JPG
If (Dir(pathForPicture & pictureName & ".jpg") <> vbNullString) Then
Cells(pictureRow, picturePasteColumn).Select 'This is where picture will be inserted
ActiveSheet.Pictures.Insert(pathForPicture & pictureName & ".jpg").Select 'Path to where pictures are stored
With Selection
.Left = Cells(pictureRow, picturePasteColumn).Left
.Top = Cells(pictureRow, picturePasteColumn).Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 130#
.ShapeRange.Rotation = 0#
End With
'End If block with .JPG
'Start ElseIf block with .PNG
ElseIf (Dir(pathForPicture & pictureName & ".png") <> vbNullString) Then
Cells(pictureRow, picturePasteColumn).Select 'This is where picture will be inserted
ActiveSheet.Pictures.Insert(pathForPicture & pictureName & ".png").Select 'Path to where pictures are stored
With Selection
.Left = Cells(pictureRow, picturePasteColumn).Left
.Top = Cells(pictureRow, picturePasteColumn).Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 130#
.ShapeRange.Rotation = 0#
End With
'End ElseIf block with .PNG
'Start ElseIf block with .BMP
ElseIf (Dir(pathForPicture & pictureName & ".bmp") <> vbNullString) Then
Cells(pictureRow, picturePasteColumn).Select 'This is where picture will be inserted
ActiveSheet.Pictures.Insert(pathForPicture & pictureName & ".bmp").Select 'Path to where pictures are stored
With Selection
.Left = Cells(pictureRow, picturePasteColumn).Left
.Top = Cells(pictureRow, picturePasteColumn).Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 130#
.ShapeRange.Rotation = 0#
End With
'End ElseIf block with .BMP
Else
'picture name was there, but no such picture
Cells(pictureRow, picturePasteColumn) = "No Picture Found"
End If
Else
'picture name cell was blank
End If
'increment row count
pictureRow = pictureRow + 1
Loop
Exit_Sub:
Range("A10").Select
Application.ScreenUpdating = True
Exit Sub
Err_Handler:
MsgBox "Error encountered. " & Err.Description, vbCritical, "Error"
GoTo Exit_Sub
End Sub
Best regards,
Trowa
I don't understand. When you make a change to either cell D5 or H5, that cells value will be used as picture name.
Best regards,
Trowa
i save my pictures in folder kelas 1 .... i try insert two photos but failed
example in cell c11 i type banana then banana.jpg come then i type apple to cell v2 then apple.jpg come .... two pictures take in one sheet ("lookupgambar").
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim InsertPict As Object
Dim bolo As Object
Dim NamaFile As String
If Not Intersect(Target, Range("$C$11")) Is Nothing Then
End If
NamaFile = ThisWorkbook.Path & "\KELAS1\" & Target.Value & ".jpg"
If Dir(NamaFile) = "" Then NamaFile = ThisWorkbook.Path & "\NoPhoto.jpg"
Call Hapus
Set InsertPict = ActiveSheet.Pictures.Insert(NamaFile)
With InsertPict
.Name = "SuperHero"
.Top = [G3].Top
.Left = [G3].Left
.Width = [G3:L8].Width
.Height = [G3:L8].Height
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.Weight = 2.25
End With
If Intersect(Target, Range("$v$2")) Is Nothing Then Exit Sub
NamaFile = ThisWorkbook.Path & "\KELAS1\" & Target.Value & ".jpg"
If Dir(NamaFile) = "" Then NamaFile = ThisWorkbook.Path & "\NoPhoto.jpg"
Call Hapus1
Set bolo = ActiveSheet.Pictures.Insert(NamaFile)
With bolo
.Name = "Bandi"
.Top = [n3].Top
.Left = [n3].Left
.Width = [n3:q8].Width
.Height = [n3:q8].Height
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.Weight = 2.25
End With
End Sub
Private Sub Hapus()
On Error Resume Next
ActiveSheet.Pictures("SuperHero").Delete
End Sub
Private Sub Hapus1()
On Error Resume Next
ActiveSheet.Pictures("Bandi").Delete
End Sub
Give the code below a try:
Private Sub Worksheet_Change(ByVal Target As Range) Dim myPict As Picture If Not Intersect(Target, Range("C11")) Is Nothing Then With Range("G3:L8") Set myPict = Range("G3:L8").Parent.Pictures.Insert(ThisWorkbook.Path & "\KELAS1\" & Target.Value & ".jpg") myPict.Top = .Top myPict.Width = .Width myPict.Height = .Height myPict.Left = .Left myPict.Placement = xlMoveAndSize End With ElseIf Not Intersect(Target, Range("V2")) Is Nothing Then With Range("N3:Q8") Set myPict = Range("N3:Q8").Parent.Pictures.Insert(ThisWorkbook.Path & "\KELAS1\" & Target.Value & ".jpg") myPict.Top = .Top myPict.Width = .Width myPict.Height = .Height myPict.Left = .Left myPict.Placement = xlMoveAndSize End With Else Exit Sub End If End SubBest regards,
Trowa
very help... thanks again