Return to VBA Code Examples

VBA – Import File Names to Clickable Links

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 9, 2023

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! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples