Looking image in a folder with vba function

nando88

Board Regular
Joined
Jun 22, 2013
Messages
118
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
What exactly would this function do?
 
Upvote 0

nando88

Board Regular
Joined
Jun 22, 2013
Messages
118
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.
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
646
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

Code:
[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:p></o:p>
 
Upvote 0

Forum statistics

Threads
1,191,546
Messages
5,987,205
Members
440,085
Latest member
MBecker79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top