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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!