Report

Insert picture in excel macro which takes the file name refrence

Ask a question V Kanagu 1Posts Friday August 23, 2013Registration date August 23, 2013 Last seen - Last answered on Nov 10, 2016 at 11:03 AM by TrowaD
Hallo! Friends

Kind to help me please
I m trying to insert picture in excel macro which takes the file name refrence from the D5 cell and insert picture in I7 cell.

Example: if D5 cell is AAA2 then the picture which will be inserted in I7 would be F:\AAA2.jpg

Note:
1. I7 cell is merged cell it cover I7 to I12 & J7 to J12
2. I change the value in D5 cell only

Pl help me if, I changed the value in D5 than the Picture inserted in I7 may automatically change according to the value of D5.

Kindly help me what is the function I want to insert in macro and how to call the macro in excel.

Sorry, for the poor English
See more 
Helpful
+5
moins plus
Hi V Kanagu,

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
Sriram- Nov 9, 2016 at 06:02 AM
Hi Trowa,

Your code to insert picture works find.

However, I have an additional requirement. i.e. when i delete the value in D5 the loaded image should also get cleared (or) when i change the value in D5, the earlier loaded image should be cleared and a new image need to be loaded in i7:j12.

Could you help me out on this.
Reply
TrowaD 2054Posts Sunday September 12, 2010Registration date ModeratorStatus January 19, 2017 Last seen - Nov 10, 2016 at 11:03 AM
Sure Sriram,

See if the code below does as requested:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5")) Is Nothing Then Exit Sub
Dim myPict As Picture

ActiveSheet.Pictures.Delete
If Target.Value = vbNullString Then Exit Sub

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


Bestr egards,
Trowa
Reply
Leave a comment
Helpful
+2
moins plus
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:
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
cakmak 4Posts Thursday November 6, 2014Registration date November 11, 2014 Last seen - Nov 11, 2014 at 06:44 AM
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.

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
Reply
TrowaD 2054Posts Sunday September 12, 2010Registration date ModeratorStatus January 19, 2017 Last seen - Nov 11, 2014 at 11:31 AM
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:
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
Reply
sachin- Jan 12, 2016 at 04:53 AM
hi trowa,

above same code I want add .jgp,png,jpeg.picure file.please help.aspa
Reply
TrowaD 2054Posts Sunday September 12, 2010Registration date ModeratorStatus January 19, 2017 Last seen - Jan 12, 2016 at 11:09 AM
Hi Sachin,

What is the issue you are experiencing? Have you tried replacing ".jpg" by either ".png" or ".jpeg"?

Best regards,
Trowa
Reply
sachin- Jan 14, 2016 at 02:11 AM
Hi Trowa,

At the moment I can only add photos of a certain file such as .jpg or .jpeg. In my older its all mixed and thats where the problem is. All of them are not the same.


thanks
Sachin
Reply
Leave a comment
Helpful
+0
moins plus
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.
Leave a comment
Helpful
+0
moins plus
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:
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
ssincerely 3Posts Sunday October 9, 2016Registration date October 12, 2016 Last seen - Oct 9, 2016 at 03:30 AM
I really found this helpful a lot but I stumbled unto something. The Pictures keep pasting over each other making the workbook increase in size. Is there a way to have the Pictures replaced the ones that are there?
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!