I just read the post by John Walkenbach Playing Songs From Excel, and like anybody else, when you read something interesting, you experiment.

The end result is a Macro that allows a user to select multiple files and import their filenames as clickable links into a spreadsheet.

I used the mp3 problem as the inspiration for this Macro, however I only provided hints on developing a complete mp3 solution. I wasn’t sure of the specifics on the problem, and you can get the real deal solution when John’s Power Utility Pak v6 is ready.

The following Macro does the following:
1. Shows a common dialog box
2. Allows a user to pick multiple mp3 files(you can change this to any file type, *.mpg for instance)
3. Imports the selected file names to clickable links in your spreadsheet(Sheet1)

The following Macro purposely does not:
1. Show the playing time for an mp3 file
I found an API solution to this, however there’s probably a better way

2. Show any of the Tag information (Artist, Year, Genre, etc.)
Read the mp3 as binary, new to me but worked great

3. Allow import from more than one directory
It currently writes over old data

I present one take on importing file names to clickable links in Excel.

Public Sub ImportMP3()

Dim counter As Integer
Dim PathName As Variant
Dim MP3name As String

Sheet1.Cells.Clear 'clear old data

'get mp3's
PathName = Application.GetOpenFilename _
("MyMusic (*.mp3), *.mp3", , "My mp3 Selector", , True)

counter = 1

On Error GoTo Cancel 'in case cancel button

  'loop through selected files
  While counter <= UBound(PathName)

     'get filename from path
     MP3name = Mid(PathName(counter), _
     InStrRev(PathName(counter), "\") + 1)

     'create hyperlink
     Sheet1.Cells(counter, 1) _
     .Hyperlinks.Add Anchor:=Sheet1.Cells(counter, 1), _
     Address:= PathName(counter), TextToDisplay:=MP3name

     counter = counter + 1
  Wend

Columns("A:A").EntireColumn.AutoFit

Cancel:

End Sub