Default folder when creating hyperlinks...

D

DistantDrums

New Member
Joined
Jun 13, 2011
Messages
7
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.
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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)

Code: 
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
 
Upvote 0
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 :

Code: 
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
260
bobaol
B
J
  • Question
How can I create a list from a single layer of a directory?
Replies
6
Views
305
PeteWright
PeteWright
S
  • Solved
For each row, create new folder based on single cell value, then save exported XML doc to matching folder.
Replies
3
Views
148
shand
S
S
  • Solved
Macro not saving exported files to where it should
Replies
2
Views
221
shand
S
R
  • Question
How to print out PDF file from hyperlinks from an Excel spreadsheet
2
Replies
11
Views
3K
John_w
J

Forum statistics

Threads
1,214,661
Messages
6,120,794
Members
448,994
Latest member
rohitsomani

Share this page

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
Back
Top