Insert picture in Excel macro which takes the file name refrence

Closed

V Kanagu

Posts: 1
Registration date: Friday August 23, 2013
Status: Member
Last seen: August 23, 2013

- Updated on Aug 27, 2019 at 11:13 AM
TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

- Mar 14, 2017 at 12:18 PM

Hello Friends,

Kindly help me!

I am trying to insert a picture in excel macro which takes the file name reference from the D5 cell and insert the 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

Please 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

How to insert picture in excel cell automatically from folder
Excel vba insert picture from folder based on cell value - Best answers
Excel vba insert picture from folder - Best answers
Insert picture in Excel: cell, shortcut, using formula - Guide
Excel Picture Inserting Macro Help - Urgent ✓ - Forum - Excel
Inserting image files from a directory specified by text string - Forum - Excel
Excel macro insert picture based on cell value ✓ - Forum - Excel
Insert Picture in Excel macro, after crop it, save it to a file ✓ - Forum - Excel

4 replies

Answer 1 / 4

cakmak

Posts: 4
Registration date: Thursday November 6, 2014
Status: Member
Last seen: November 11, 2014

7
Nov 6, 2014 at 10:20 AM

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.

Answer 2 / 4

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
Nov 6, 2014 at 11:04 AM

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

Posts: 4
Registration date: Thursday November 6, 2014
Status: Member
Last seen: November 11, 2014

7
Nov 7, 2014 at 02:42 AM

Thanks a lot Trowa,

I will try it and give you feedback.

Best Regards,
Cakmak

cakmak

Posts: 4
Registration date: Thursday November 6, 2014
Status: Member
Last seen: November 11, 2014

7
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

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
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

sachin > TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

Jan 12, 2016 at 04:53 AM

hi trowa,

above same code I want add .jgp,png,jpeg.picure file.please help.aspa

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
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

Answer 3 / 4

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
Aug 26, 2013 at 10:58 AM

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.

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515 > Sriram
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

bandi
Feb 11, 2017 at 07:20 AM

hi... anyone can help me make two target when take picture ... like D5 and h5 thanks

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
Feb 14, 2017 at 12:00 PM

Hi Bandi,

Replace the 2nd code line with:
If Intersect(Target, Range("D5","H5")) Is Nothing Then Exit Sub

Best regards,
Trowa

bandi
Feb 14, 2017 at 07:21 PM

hi TrowaD,
thanks so much... one question again, please help me... how insert two or three pictures with different target... example : D5 to apple.jpg and H5 to orange.jpg .... i had try like code up but failed ... please and thanks again for your time

bandi
Feb 22, 2017 at 03:25 AM

this is my code please corectly and thanks so much for your time....
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

Answer 4 / 4

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
Jan 18, 2016 at 11:58 AM

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

Posts: 3
Registration date: Sunday October 9, 2016
Status: Member
Last seen: October 12, 2016

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?

Linesh Lohidakshan
Mar 13, 2017 at 12:38 PM

Pls. Thank you very much for the codes. But in some cases if the images are missing the next image is copied in to that cell instead of showing No picture found.

Can you pls help.

TrowaD

Posts: 2886
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 27, 2022

515
Mar 14, 2017 at 12:18 PM

Hi Linesh,

Just tested the code from post 10 and it shows "No Picture Found" when the image isn't found. Maybe the picture is overlapping the text?

Best regards,
Trowa

Similar discussions

Excel VBA copy and paste loop with logic condition

Conditional formatting: change fontcolor based on another cell

Excel - A macro to create new workbook and copy data

Macro to email picking up email address in one cell using lookup

Pop up message stating empty cells

How to copy visible cells after filter in VB

Similar discussions

Subscribe To Our Newsletter!