Default folder when creating hyperlinks...

[DistantDrums]

Hi. Noob here. I have a doc with a very long A Column filled with titles of songs that are stored as audio files on my system, and I'm wanting to change all these mere titles into hyperlinks which would call up RealPlayer (or whatever) and play each audio clip when a title is clicked. I can do that, but each time I right-click/hyperlink, the 'Look In' dialog defaults to My Documents, and I have to browse to the correct folder, which is very tedious. We're talking over 2,500 titles.

Excel Help's entry for the Hyperlink Function didn't seem to address this. I did find a forum discussion about changing the Hyperlink Base by going to File>Info>Show All Properties and entering a path into the Hyperlink Base field, but after doing this, right-click/hyperlink still defaults to My Documents. (I'm thinking maybe this solution was intended for docs with already existing hyperlinks?, which isn't my situation...)

So, how do you get the Insert Hyperlink dialog to open to a different specified folder automatically?

Also, as a bonus, assuming there is a way to change the default folder, would there then be a way to simply select the entire column and convert all the titles to hyperlinks in one operation? Does it help that each title in the column happens to be identical with its corresponding audio file on the drive (well, minus the file extension)? Just trying to keep from doing 2,500 repetitions of anything.

Thanks for any insights. Excel 2010, Vista 64-bit.

- Bob K.

 

[Jaafar Tribak]

Are all the audio files in the same folder ?

 

[DistantDrums]

Hi. No, not currently. The folders are alphabetized (an A folder for the A titles, a B folder for the B's, etc...). But I'd gladly copy/paste all the audio files into a special, single folder if that would help solve the problem.

- Bob K.

 

[Jaafar Tribak]

Ok. If you put the audio files into a new folder say for example folder : "C:\AudioFiles" and the column filled with the song titles is Column A:A then you can try the following small Macro : (Note :Activate the worksheet with the song titles before running the macro)

Sub Test()

    Dim i As Long
    
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        With ActiveSheet
            .Cells(i, 1).Hyperlinks.Add .Cells(i, 1), "C:\AudioFiles\" & .Cells(i, 1)
        End With
    Next
    
End Sub

 

[Jaafar Tribak]

I've just read about the missing audio file extensions in the titles .

If the extensions are all the same (for ex .mp3) you just add the ext string at the end as follows :

Sub Test()

    Dim i As Long
    
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        With ActiveSheet
            .Cells(i, 1).Hyperlinks.Add .Cells(i, 1), "C:\AudioFiles\" & .Cells(i, 1) [COLOR=Red][B]& ".mp3"[/B][/COLOR]
        End With
    Next
    
End Sub

If the extensions are different from one title to another then we should try a different code.

 

[DistantDrums]

Perfect!!! Copied all the files over to a single folder (and yes, they were all the same type - .m4a [iTunes library]), ran the code, and presto: instant full column of hyperlinks!

Thanks, Jaafar.

- Bob K.