VBA – Import File Names to Clickable Links

This is a Macro that allows a user to select multiple files and import their file names 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.

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