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
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 EasyStop 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!