formula to insert pictures from another file into excell

January 11, 2014 at 20:40:52
Specs: Windows 7
Trying to make a catalogue with pictures in excel. the pictures are in a file called c:\documents\graphics. I would like a formula that relates to the graphice in a1,a2,a3,and so on while the picture name is in b1,b2,b3,and son on.

message edited by trevsquestion

See More: formula to insert pictures from another file into excell

Report •

January 12, 2014 at 07:18:45
What do you mean by "a formula that relates to the graphice in a1,a2,a3..."

Are you looking for a hyperlink to the picture?

Right-click a cell and choose Hyperlink
Navigate to your photo to get something like this in a cell:


or use the HYPERLINK function:

HYPERLINK(link_location, [friendly_name])

=HYPERLINK("C:\Users\user_name\Documents\AngleFish.jpg", "Angel Fish")

The [friendly_name] is an optional argument which allows you to put the picture name in the same cell as the hyperlink.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 12, 2014 at 07:43:28
Another option is to use VBA to create the list and hyperlinks.

The following code, which is a modified version of the code found here...

... will create a new sheet and put a list of all jpg files from the specified path in Column A. The file name will be displayed and will be a hyperlink to the file. You'll need to change the path to the jpg files in the line indicated:

Sub InsertFilesInFolder()
Dim sPath As String, Value As String
Dim WS As Worksheet
  Set WS = Sheets.Add
' *** Change path to pictures in the following line:
   sPath = "C:\Users\user_name\Documents\"
    Value = Dir(sPath, &H1F)
    WS.Range("A1") = "Filename"
  Set StartCell = WS.Range("A2")
     Do Until Value = ""

         If Right(Value, 3) = "jpg" Then
            StartCell.Hyperlinks.Add Anchor:=StartCell, Address:= _
              sPath & Value, TextToDisplay:=Left(Value, Len(Value) - 4)
            Set StartCell = StartCell.Offset(1, 0)
         End If
      Value = Dir
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 13, 2014 at 02:56:31
Hi DerbyDad03
Thank you for your response I tried your second formula in macros but it only opened a new spread sheet. I should have explained myself better. What I have done is download a file from myob which contains all the information which I need to make up a catalogue of my products numbering 3000. I split the products into different groups and made sheets with about 100 products in each sheet. The columns contain info such as price gst and description but the picture column only contains the name of the picture not the picture itself. for me to link and open every picture would be too time consuming. I thought if I created a blank column and added a formula that links and installs the picture into that cell taking the picture name from the corresponding column it would save lots of time as we are always getting new products to add to the catalogue in different pages. Hope that explains it a little better.
Thanks again

Report •

Related Solutions

January 13, 2014 at 14:14:12
re: "I tried your second formula in macros "

Just to make sure we are using the correct terminology, my second suggestion is called a "macro", not a "formula in macros"

re: "it only opened a new spread sheet"

If you didn't use the correct path to the files or if the files were not jpg files then the code will indeed only create a new sheet and not do anything else. Well, it will actually do other things, such as look for the jpg files in the specified path, but if they are not found, nothing will be inserted.

As for your request, the following code inserts each picture from the specified path and places it in a cell in Column A.

The exact name of the file, without the extension, must be in Column B.

As written, the code loops through B1:B4, picking up the file name, appending .jpg to the name and then inserting the picture into the corresponding row in Column A. It also resizes the picture to sort of fit in the cell. By “sort of” I mean that the pictures end up to be the size of a standard Excel cell. You’ll have to play with those settings to get you pictures to be the size that you want.

If you paste the code into the VBA editor, change the Path to match the path to your folder and then use F8 to single step through the code, you should be able to see each picture as it is added and then resized.

I suggest that you experiment with just a few pictures instead of having it run through 100 pictures and then find out that they are all the wrong size, etc.

A tip: If, during your experimenting, you want to delete a number of pictures all at once, press F5, choose Special, then Objects. This will select all of the pictures and you can press Delete to get rid of all of them.

Here’s the code:

Sub InsertPictures()

'Loop through Rows 1 - 4
   For nxtPic = 1 To 4

'Insert jpgs based on text in Column B
       ActiveSheet.Pictures.Insert _
           ("\\pcg\root\Users\user_name\My Pictures\" & _
                   Cells(nxtPic, 2) & ".jpg").Select
' Move the picture to the cell and resize
           With Selection
             .Left = Cells(nxtPic, 1).Left
             .Top = Cells(nxtPic, 1).Top
             .ShapeRange.LockAspectRatio = msoFalse
             .ShapeRange.Height = 20#
             .ShapeRange.Width = 40#
             .ShapeRange.Rotation = 0#
           End With
'Activate B1
    Cells(1, 2).Activate
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 14, 2014 at 21:51:55
hi DerbyDad03
Thank you for all your help so far I am only new to this so please excuse my terminology as I am learning as I go. Used your macros from the other day and it loaded all the links to pictures on another sheet just fine. Tried to use new code but it came up with run-time error '1004' when run macros was entered. pressed debug and it highlighted the area as shown below.
Sorry could not paste picture in but the highlight was from end of "Column B" to "Select". Checked file path and it is the same as for the macros that worked. Have played with code but no success. Any idea would be appreciated.
By the way thanks for the heads up on F5 as I had no idea how to delete the pictures previously.

message edited by trevsquestion

Report •

January 15, 2014 at 11:27:18
Without actually seeing the code you are trying to use, it is hard for me to tell why you are getting an error on that line.

I can cause that error in a few different ways:

1 - If the path is incorrect, e.g. a \ is out of place or missing, etc.
2 - The filename in Column B is incorrect e.g. Picture1 instead of Picture 1. (note the space)
3 - The files themselves do not have an extention of .jpg e.g. they are gif or png files.

What are you using for a filename in Column B? It should be the filename without the extension. e.g. Picture 1 not Picture 1.jpg. As I mentioned in my previous post, the code appends ".jpg" before it inserts the picture.

Does any of that help?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Ask Question