Creating multiple hyperlinks to files in a folder

rvk1949

New Member
Joined
Feb 15, 2005
Messages
4
Hi

I have a folder containing family pictures which I have collected over a number of years. I want to create a spreadsheet listing these photos in column A, hyperlinked to the relevant picture and then have a list of family members in row 1. Then I can indicate which members appear in which photos. Is there any way to automatically fill the column with the hyperlinks, there are about 3000 photos and the names do not follow any particular pattern as they have been collected from various sources.

Any ideas would be much appreciated as adding te links manually would be an enourmous task.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
insert this code into the vba of your sheet, and execute it.
It gives a list of all files in "currentpath".
change it to the map you like (ie. "C:\Myfotos")

Code:
Sub filelist()

    Dim hyperlinkto As String
    Dim currentcell As Range
    Dim currentpath As String
    
    currentpath = ".\"
    
    Set currentcell = Range("A1")
    
    hyperlinkto = Dir(currentpath & "*.*")
    
    Do
        currentcell.Hyperlinks.Add currentcell, currentpath & hyperlinkto
        hyperlinkto = Dir
        Set currentcell = currentcell.Offset(1, 0)
    Loop While hyperlinkto <> ""

End Sub
 

rvk1949

New Member
Joined
Feb 15, 2005
Messages
4
First, many thanks for the prompt response.

We are almost there, but I have run this and it gives me a link to the folder in A1 and then stops with a Runtime error 5, invalid procedure call or argument on the line

hyperlinkto = Dir

Rick
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
hmm, what excel version do you have? try:

hyperlinkto = Dir()
 

rvk1949

New Member
Joined
Feb 15, 2005
Messages
4

ADVERTISEMENT

Thanks aagin but same result I'm afraid, it is Office 2000 pro if that helps
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
I don't know why it doesnt work. you have copied the exact code?
 

rvk1949

New Member
Joined
Feb 15, 2005
Messages
4
Whoops, sorry, it does work, just needed the \ on the end of the path

Many many thanks Harvey you have saved me a great deal of effort
 

Watch MrExcel Video

Forum statistics

Threads
1,122,649
Messages
5,597,368
Members
414,139
Latest member
okela0

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