How to insert a picture from a designated folder

[Kalilla]

Hello, first time poster so I apologize if I get something wrong here.

My goal I hope to achieve here is to have excel read the contents of cell A1, which contains the file name of the picture, then go to the folder which has 9728 different pictures inside and find the one with that file name and have it change accordingly.

I was able to make the indirect magic trick from a few years ago work, but as you could imagine that requires you to have all 9728 pictures in your workbook which I was able to do after a few days and a script that posted the pictures for me and after all that hard work it was just extremely slow. It worked, it was what I wanted, but it was slow. Pull your hair out kinda slow.

I've done a search on the site which had topics like this one and it had VBA scripts posted in them that I tried to get to work for my project but it kept having debugging errors which I do not have the experience or knowledge to find out why they wouldn't work.

I wish I could provide some type of code I've been working on out of respect but I just don't have the knowledge to do so. I understand if you are reluctant to help because of this and I completely understand and I can do nothing but apologize.

I'm going to describe what I've been trying to get done the past few days after starting over with a new way of doing this in mind.

I have 16 different cells each containing a different file name and each cell would be linked to its own 32x32 picture. When I update a cell with a new filename I would like for it to update the picture it is linked to. It's not 1 cell updating 16 different pictures, hopefully . I hope that since the workbook is not holding all those pictures and instead looking for it in the designated folder on my computer it would cut down the load excel has to carry and it would work smoothly. This of course is just me hoping really, I have no clue if it will or not and I know I'm over my head with this but if you could spare some help I would truly appreciate it greatly and I hope that I could learn a lot more about excel from this experience.

Windows 7
Excel 2010

 

[yky]

I don't know where you want to insert the pictures. Assuming you want to insert them to column A, then you need to insert a column to column A first.

The following code should work.
========================================
Private Sub insert_pictures()
Dim myPath As String
Dim fileName As String
Dim rng As Range
Dim cell As Range
Dim p As Object
Dim tempWidth As Double
Dim tempHeight As Double

Application.ScreenUpdating = False

ActiveSheet.Pictures.Select

Const myPath = "C:\...\" 'set your path here

Cells(1, 1).EntireColumn.Insert 'insert a column to A

'set up the range for inserting pictures
Set rng = Range(Cells(1, 2), Cells(10000, 2).End(xlUp))

for each cell in rng

Set p = ActiveSheet.Pictures.Insert(myPath & cell.offset(0,1).value)

'get the width and height of the picture to be used in AddPicture()
'then delete the picture
tempWidth = p.Width
tempHeight = p.Height
ActiveSheet.Shapes(p.name).Delete


'insert picture
cell.Select
Set p = ActiveSheet.Shapes.AddPicture(fileName:=myPath & fileName , _
LinkToFile:=False, SaveWithDocument:=True, Left:=cell.Left, Top:=cell.Top _
, Width:=tempWidth, Height:=tempHeight)

cell.RowHeight = p.Height + 2

'if column is too narrow, widen it
'can only change cell.columnwidth, not cell.width
If cell.Width <= p.Width Then
cell.ColumnWidth = p.Width / cell.Width * _
cell.ColumnWidth
End If

p.Top = cell.Top + 1


End If

Next

Application.ScreenUpdating = True

 

[Kalilla]

Thank you for the quick reply, I'll try it out.

Edit:

Compile Error:
Duplicate declaration in current scope.


It's highlighting "Private Sub insert_pictures()"

 

[Kalilla]

Am I doing something wrong? I created a new project unrelated to mine to make sure it was untouched and working properly then created a new Module to paste your code in and edited in the file path of the folder.

Private Sub insert_pictures()
Dim myPath As String
Dim fileName As String
Dim rng As Range
Dim cell As Range
Dim p As Object
Dim tempWidth As Double
Dim tempHeight As Double

Application.ScreenUpdating = False

ActiveSheet.Pictures.Select

Const myPath = "C:\images" 'set your path here

Cells(1, 1).EntireColumn.Insert 'insert a column to A

'set up the range for inserting pictures
Set rng = Range(Cells(1, 2), Cells(10000, 2).End(xlUp))

for each cell in rng

Set p = ActiveSheet.Pictures.Insert(myPath & cell.offset(0,1).value)

'get the width and height of the picture to be used in AddPicture()
'then delete the picture
tempWidth = p.Width
tempHeight = p.Height
ActiveSheet.Shapes(p.name).Delete


'insert picture
cell.Select
Set p = ActiveSheet.Shapes.AddPicture(fileName:=myPath & fileName , _
LinkToFile:=False, SaveWithDocument:=True, Left:=cell.Left, Top:=cell.Top _
, Width:=tempWidth, Height:=tempHeight)

cell.RowHeight = p.Height + 2

'if column is too narrow, widen it
'can only change cell.columnwidth, not cell.width
If cell.Width <= p.Width Then
cell.ColumnWidth = p.Width / cell.Width * _
cell.ColumnWidth
End If

p.Top = cell.Top + 1


End If

Next

Application.ScreenUpdating = True
End Sub

It highlights "Private Sub insert_pictures()" in yellow and then selects the text "myPath =" in blue giving this error message:

Compile error:

Duplicate declaration in current scope