Help matching images in a folder to a worksheet

[Cruiser69]

Hi all.

I've searched but can't find the answer I am looking for.

Each week I get a manifest sent from a vendor with product codes in Column A

I have a folder with images which some of them match the filename in the document.

At the moment, I have to copy the code from the worksheet and search in the image folder to see if it matches an image.

Some codes on the document are new so will not be found in the image folder.

All I need is to show on the worksheet, say "Y or N" in column B if the corresponding image has been found. I don't want to download the image.

If it says "N" then I will search the vendor web site for the image and download it.

If there is a formula or Macro I can use it will help a lot.

Thanks for looking,

Graham

 

[Micron]

So what is your level of coding expertise? Maybe you're just looking for ideas and will take it from there with some Googling?
I think you would loop over the sheet column A with the image names (if that's what you have) and
if .Offset(0,1) is Y go to next row in A
if .Offset(0,1) is not Y then
- use Dir function with the folder path and file name.
- - If it finds the file name it returns it, put Y in col B on that row
- - If not, put N in col B in that row

If you want to get fancy, at the very beginning use **msoFileDialogFolderPicker to return the folder path to a variable and use that as a prefix to the image name it is looking for on each row that is not already Y. Processing only cells that are not Y will help to speed up the loop by not looking for image files that were previously found, but will look again for N or blank.

** Note- this feature allows you to pick a path to a folder but won't display files in that folder in the dialog. If you need to see files in the folder to be sure it's the right one, maybe use msoFileDialogFilePicker instead.
HTH

 

[Tetra201]

See if the following user-defined function (UDF) works for you.
Usage example: =test(A2,"C:\Users\JDoe\Desktop\Product Images")

Function Test(s As String, f As String) As Boolean
    Application.Volatile
    Test = Dir(f & Application.PathSeparator & s & ".*") <> ""
End Function

 

[Cruiser69]

Thanks for your replies. My expertise is limited.
The loop option sounds good.
Say the sheet has code e.g. A123 and the jpeg image has the same code A123
How would I add that as a VBA

 

[Micron]

What about post 3? If it works for you it's much simpler. A potential drawback is that if you have multiple image folders you would need to change the folder path in the formula as you go. FWIW I would have just ended the path part of the formula with \ and forget about Application.PathSeparator. At least I think that would be OK.

 

[Cruiser69]

So does the formula go into b2 of the document and the module into the sheet 1 code as it is or does this have to be modified

 

[Tetra201]

Yes, the formula goes into cell B2, and the VBA function goes into a regular standard module (not in the sheet or workbook code section). No modifications needed.

 

[Micron]

I believe the message means "standard module" which should be taken as not a sheet module?

 

[Cruiser69]

Sorry to seem stupid, but after I add the below in a standard module, what do I do then

Function Test(s As String, f As String) As Boolean
Application.Volatile
Test = Dir(f & Application.PathSeparator & s & ".*") <> ""
End Function

 

[Cruiser69]

Don't worry, it was me being stupid. Got it working now.
Thanks for all your help.