How to input pictures automatically based on cell input?

B

bsharp

I am trying to create a template so I can have 6 photos load automatically to
one of the worksheets. The photos will come from a folder on my hard drive
and Excel will look in the folder based on my input of one cell. The photos
will be labelled with numbers 1-6. So the path will be exactly the same each
time, the only variable will be the one folder name.

I have created a large worksheet to track my real estate properties for
sale, and one of the worksheets is a flyer. So the pictures need to be placed
precisely where they are supposed to go. I realize Publisher is a better
flyer program, but I have everything else in Excel, and this way the price,
address, etc. will auto-fill for me based on what I input on the first
worksheet. I save all the workbooks as the property address, so I was hoping
to just insert the address into the filepath to go grab pictures #1, 2, etc.
 
B

bsharp

Thanks for trying, but that doesn't really address my issue. Looks like that
link tells me how to insert a picture one time.

But I'm looking to enter the name of a folder in a cell, then have Excel
insert that value into the path to go retrieve the picture at that location.
So if I put a different folder name in a cell, a different picture will be
retrieved.

For example, I want to have a cell titled - Property Address. Then if I
enter "123 Main St" the picture at \pictures\123 main st\1.jpg will be
retrieved. But I want the picture placed on another worksheet.
 
S

Sheeloo

You can adapt that code to read the path from your cell... you can set it to
execute automatically whenever the input cell changes.
 
B

bsharp

Thanks.

Can you help me with the syntax? Let's say I have the folder name in A1, how
do I change this line? Do I just put =A1 instead of "FolderName"? Or does it
need a bracket or something to tell it it's a cell reference and not text?

InsertPicture "C:\FolderName\PictureFileName.gif", _
 
S

Sheeloo

The macro which calls the macro to insert the picture is
Sub TestInsertPicture()
InsertPicture "C:\FolderName\PictureFileName.gif", _
Range("D10"), True, True
End Sub

You can modify it to
Sub TestInsertPicture()
folderName= ThisWorkbook.WorkSheets("Sheet1").Cells(1,1)

InsertPicture folderName & "PictureFileName.gif", _
Range("D10"), True, True
End Sub

Cells(i,j) refers to ith row and jth column
Replace Sheet1 by the actual sheet name
It will insert the picture in D10... you can change that too. You can also
put pictureFileName.jpg in another cell and refer to that or the whole
path+name in A1 and remove the part & "PictureFileName.gif"
 
B

bsharp

I appreciate your efforts, really I do. But I still don't think this solves
my problem.

You say, "Replace Sheet1 by the actual sheet name"

I don't want to have to go into the macro and change the sheet name. I want
to be able to enter the folder name into a cell in my worksheet, and then
based on that input the macro goes and looks in that location.

Is that what your suggestion will do? It just doesn't look like it to me,
but I'm no macro-expert!
 
B

bsharp

Beautiful, thanks so much for doing that.

At first it didn't work, but then I added a "\" right before "1.jpg" and
then it worked.

Only problem now is it put the picture is very large, I want it to hit a
smaller defined area, but I can search that out on the forums here to see if
that is discussed. I think I saw some other threads about sizing and placing
the image.

But you got me past the hard part, thanks!
 
S

Sheeloo

You are welcome...
The second function at the page I had shared centers the picture across you
range of cells...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top