Looking image in a folder with vba function

[nando88]

I have been trying to find out a way to lookup a picture in a folder with the name specified in a cell, but instead of using a macro and specifying each cell, I want to make a function and by doing so tell it which cell contains the picture name.
I have searched the web and found pages like:
Excel - A macro to insert pictures
That give code for macros, but I can't figure out a way to change that code and use it to make an excel vba function.
Can someone please help me with this task?
Thanks.

 

[Norie]

What exactly would this function do?

 

[nando88]

It will act as a vlookup function in excel, looking up a cell value in the names of the pictures in a folder and if the picture exists, it will return the picture and if not then it will return nothing.

 

[Norie]

Return the picture name or the actual picture itself?

If it's the latter and you intend to use the function as a UDF then I don't think that's going to be possible.

 

[nando88]

The actual picture.

 

[Perpa]

nando88,
The following function (thanks again to Trebor76) requires two items to add a picture:
1 - The complete path 'PicPath' in this format: 'c:\yourpath'
2 - the picture name 'pn', in this format: 'filename.jpg'
The picture would be placed in cell A1. You can change the location to suit
To use the function: Enter the formula: '= insert1(c:\yourpath, thefilename.jpg)' into any cell. Replace ' c:\your path, thefilename.jpg' with your values, OR store those values in different cells such as J1 and J2 and the formula would become: '= insert1(cells(1,"J"),cells(2,"J"))'

I hope this is helpful.
Perpa

[FONT=Calibri][SIZE=3][COLOR=#000000]Function insert1(PicPath, pn)
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Formats Column A Pictures
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]WithActiveSheet.Pictures.insert(PicPath)
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With.ShapeRange
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].LockAspectRatio = msoTrue[/COLOR][/SIZE][SIZE=3][COLOR=#000000]'Ifyou uncomment both Width and Height lines below change to 'msoFalse'
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'.Width =50[/COLOR][/SIZE][SIZE=3][COLOR=#000000]'Adjust to change the WIDTH ofyour pictures - COMMENTED OUT
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Height =198[/COLOR][/SIZE][SIZE=3][COLOR=#000000]'Adjust to change the HEIGHT ofyour pictures
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Left =ActiveSheet.Range("A1").Left
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Top = ActiveSheet.Range("A1").Top
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Placement = 1
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].PrintObject =True
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT]

<o></o>