Create hyperlink

Hi,

I have thousands of files in a folder and I want to extract the part of filename and create their hyperlinks. My files in a folder (VCD) look like this:
US45102950_25165521_Feb07_1.jpg
US45102950_25165521_Feb07_1.pdf
US45102950_25165521_Feb07_1.txt
US45102950_25165521_Feb07_1.tif
US45102950_25165521_Feb07_2.jpg
US45102950_25165521_Feb07_2.pdf
US45102950_25165521_Feb07_2.txt
US45102950_25165521_Feb07_2.tif

My excel file in different columns(A, B, C, D, E, F, G, H) should look like
A B C D E F G H
US45102950 25165521 Feb07 1 jpg pdf txt tif
US45102950 25165521 Feb07 2 jpg pdf txt tif

When I will click on any of jpg , pdf ot txt link, it should open its respective file. Actually each file has four different extenstions like jpg, pdf, txt, and tif but the filename is same. I want to extract the information of filename after each "_" in different column and with their hyperlinks of jpg, pdf, txt, tif files in a single row. So that will have information of four files in a single row. That will really help me in managing my files and viewing those files.

I have just started learning VBA programming. Please help me in making the code.

Thanks in advance

Manish

i have attached two files....carefully follow the steps listed below

GetFileInfo.xls is the file which will help u to list all the files in the directory...

before proceeding...copy all your files(for which hyperlink to be created) into a single new folder....

Now, open Getfileinfo.xls file (dont save anywhere) and click the button....thru open dialog box...open the new folder which you created as mentioned above......

a macro will run to list all the files and properties in a NEW excel file....(no need to save this new file also)

Now take my Filenames.xls and copy it to the folder (same folder as mentioned above) which contains all the files and open it....

Copy the files names from NEW excel file created by Getfileinfo.xls macro....and paste all the file names in Column A...

Run the 'Primary' Macro....

Hyperlinks would be created automatically to all the files....

Remember To...

list all the files names using Getfileinfo.xls Macro.
Filenames.xls should be copied to the folder in which you have all the files. (ONLY after running macro Getfileinfo.xls)

Hope this helps u

Regards,
guru

Hi guru,

Thanks for solution but when I clicked on GetFileInfo.xls and tried to run the macro, it gave me an error-Compile error variable not defined. It highlighted this line: "With Application.FileDialog(msoFileDialogFolderPicker".

Can u again look at the code. That will be great.

Thanks

Manish

Hello Manish86,

I wrote a similar macro for Ashish82. It could be changed to meet your needs. Click on the link below to view the thread.

Creating Hyperlinks to Files

Sincerely,
Leith Ross

GetfileInfo.xls macro is working fine for me....

first keep the files in the new folder and then run this macro....

or else try someother macro which lists all the files in the directory....and then run Primary Macro in solution.xls

Thanks

guru