Return Closest Matching Filename - Macro

Hello!

I have an odbc report that inserts data (product number) with trailing spaces. I currently have a macro that inserts an image from a specified file location based on the selected cell value. It works great when I manually enter a product number to run the macro off of as it obviously does not contain trailing spaces.

I would like for the macro to search the specified file location based on the leading characters of the value in the selected cell and return the closest match possible. All with a msgbox that the exact file was not found but "the closest file is ...".

For example, I need an image returned for product number 12345WHITE(+SPACES). If I only have a file named 12345, I'll need a message box = "12345WHITE not found. Inserting 12345" and insert the correspondingt image. If the folder also contains 12345WHI, it should attemp to return this file instead as it's the closest match.

Can this be done?

Thanks ahead to you wizards!

E

Workbook is sensitive. Substitue attached.

IF ITEM IS 4321GOLD, MACRO WILL RETURN IMAGE FILE 4321G.JPG AS OPPOSED TO 4321.JPG IF ITEM NOT FOUND
IF ITEM IS JW005, MACRO WILL RETURN IMAGE FILE JW00.JPG AS OPPOSED TO JW006.JPG "
IF ITEM IS 4321GOLD, MACRO WILL RETURN IMAGE FILE 4321WHITE.JPG (IF ITS THE NEXT CLOSEST MATCH).

Please Login or Register  to view this content.

Originally Posted by Leith Ross

Hello enidnunez,

Please post your workbook and include more before and after examples. This will speed up developing a solution to your problem.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

Can I use the Like Operator to accomplish part of this? If yes, how?

I have used other methods to achieve intended results.