Inserting pictures into Excel based on file name

Closed
Report
Posts
4
Registration date
Wednesday May 1, 2013
Status
Member
Last seen
May 6, 2013
-
Posts
4
Registration date
Wednesday May 1, 2013
Status
Member
Last seen
May 6, 2013
-
Hi,

I found the below macro on another thread and used it successfully to insert pictures into Column A based on file names in Column B.

However, when I move the Excel File to another system, the pictures don't show up. I presume the file is still trying to access the original picture folder for the pictures. Can you please help so that the pictures stay in the file irrespective of where it is? I cannot even break links because that option is greyed out.

----------------------------------------------------------------------------------
Option Explicit

Sub Picture()
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 = "B"
picturePasteColumn = "A"

pictureRow = 2 '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:\Users\Administrator\Desktop\LC\"
'loop till last row
Do While (pictureRow <= lastPictureRow)

pictureName = Cells(pictureRow, "B") '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
----------------------------------------------------------------

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
what version of execl?

what happens, if
a. Import a picture into excel using the macro
b. then rename file name of that picture to some thing else
Does excel still shows you the picture ?

What if you move the excel once the picture were imported to some other location in the same pc. Does the picture still show ?
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
4
Registration date
Wednesday May 1, 2013
Status
Member
Last seen
May 6, 2013

Can someone please help.....
Posts
4
Registration date
Wednesday May 1, 2013
Status
Member
Last seen
May 6, 2013

Thank you for your response.

I'm using Excel 2013.

I tried your suggestion, the picture doesn't show up once I change the name of the picture file.

The picture still shows when I move the Excel file to another location on my laptop.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I have tried both with 2007 and 2003 but I cannot replicate your issue.
Posts
4
Registration date
Wednesday May 1, 2013
Status
Member
Last seen
May 6, 2013

OK, I will try it on another system running 2007 and post my findings. Thank you for your time.