Creating multiple hyperlinks to files in a folder

[rvk1949]

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.

 

[Harvey]

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")

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]

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]

hmm, what excel version do you have? try:

hyperlinkto = Dir()

 

[rvk1949]

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

 

[Harvey]

I don't know why it doesnt work. you have copied the exact code?

 

[rvk1949]

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