Solved: Excel Macro Help - Hyperlinking

[artinj]

Hi everyone! This is my first post here so i hope I am providing here all the details regarding the following:

1)I have an excel table which has what are called Document numbers in the following format: 123456789 (i.e. standard 9 characters long). For example all the records are in column A as follows (in this example there are 15,000 unique records/document#):

Cell Document#
(A1) 545698789
(A2) 598698745
..............
(A15000) 598765412


2) Then, in a storage drive(E:\Images\ ) I have more than 100,000 images in PDF or MDI format all having a document # as name, for example 545698789.pdf. All 15,000 document# listed in above excel table exist as images in the "E:\images\" folder path.

Solution:
Can there be a macro that will automatically loop through the excel document# list in column A, and then lookup each document# in the "E:\images\" folder path, and then return a hyperlink for that image in the adjacent cell in column B??

An optimal solution would be if the macro could actually copy the image file and bring it (insert/embed) in the excel file in column B as an icon. I assume this second request is probably more complicated, so I'll be happy if the first one can be done.

Thank you in advance for your input on this issue. I greatly appreciate all the help.

Regards,

Arti

[Ken Puls]

Hi Arti, and welcome to VBAX,

Do you know from the Excel file what the extension is? If you do, you could just write a formula to create your hyperlinks and extend it down. Eg:

=HYPERLINK("E:\images\" &A1&".pdf")

(Returns the path to A1's value.)

The image would definitely need VBA though...

[rbrhodes]

Hi arti,

Took this as a challenge.

This code will import a picture OR a file based on the file extensions given to it.

In the example attached it's looking for either .mdi or .pdf as the file extensions.

I understand mdi is simply a jazzy tiff format so the picture part of this code MAY work as is (I doubt it tho)

Let me know if it doesn't work for you and post an .mdi file as an example for me to work with and I'll attempt to adapt it.

[artinj]

Guys, you are the best!!! I just tested both options and both worked superbly. I can't find enough words to thank you on this.

Best regards,

Arti