Importing music titles into a spreadsheet

[Russ]

I use a spreadsheet with macros that lists all the file names of my songs
from my music folder into an excel spreadsheet ( I can post ir if it will
help). My new comupter has Office 2007 and the macro will not work. I do not
know macros well enough to fix it. Does anyone know of a spreadsheet I can
dowmload that would look into a folder and copy the file names into an execl
spreadsheet?

Thanks you for your help,
Russ

 

[Gord Dibben]

Several methods to accomplish this.......I like Tushar's best if importing to
Excel.

To add a "Print Directory" feature to Explorer, go to
this KB Article.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&

Or you can download Printfolder 1.2 from.....

http://no-nonsense-software.com/freeware/

I use PF 1.2 and find it to be more than adequate with custom
features.

OR Go to DOS(Command) prompt and directory.
Type DIR >MYFILES.TXT

All the above create a *.TXT file which can be opened in Notepad or
Excel.

One more method if you want to by-pass the *.TXT file and pull
directly to Excel is to use Tushar Mehta's Excel Add-in. This allows filtering
and sorting once you have the data in Excel.

http://www.tushar-mehta.com/ scroll down to Add-ins>Directory
Listing.

Download the ZIP file and un-zip to your Office\Library folder.


Gord Dibben MS Excel MVP

 

[Guest]

See:

http://vbaexpress.com/kb/getarticle.php?kb_id=781

 

[Dave Peterson]

You may want to look at John Walkenbach's version:
http://www.j-walk.com/ss/excel/tips/tip103.htm

 

[Don Guillett]

This one should do what you want. I tested recently.

 

[Don Guillett]

This is one of my own from a while back. Needs modernizing.
Here is one I have used in my "songs.xls". Written a couple of years ago.

Sub FindFiles()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'sets Erase or Append option
lastrow = Range("a65536").End(xlUp).Row
If lastrow = 4 Then lastrow = 5 Else lastrow = lastrow
'MsgBox lastrow
If UCase([a3]) = "E" Then
Range("a5:f" & lastrow).ClearContents
lastrow = 4
ElseIf UCase([a3]) = "A" Then lastrow = lastrow
End If
'Sets Musicpath
If Right([a1], 1) <> "\" And Left([a1], 1) <> "_" Then x = "\"
If IsEmpty([a2]) = False And Right([a2], 1) <> "\" Then y = "\"
musicpath = [a1] & x & [a2] & y
'Finds Files
With Application.FileSearch
.NewSearch
.LookIn = musicpath
.SearchSubFolders = True 'False
.MatchTextExactly = False
.Filename = ".mp3" '*.mp3* did not work in 97
If .Execute(msoSortOrderDescending) > 0 Then
'MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox Mid(.FoundFiles(i), Len(musicpath) + 1, 2)
If Mid(.FoundFiles(i), Len(musicpath) + 1, 2) <> "__" Then 'added for
__INCOMPLETE

x = Application.Find("\", StrReverse(.FoundFiles(i))) - 2 'must have
function before xl2000
y = Application.Find("-", StrReverse(.FoundFiles(i))) - 1

Cells(i + lastrow, 1).Value = Mid(.FoundFiles(i), Len(.FoundFiles(i)) - x,
x - y)
x = Application.Find("-", .FoundFiles(i)) + 1
Cells(i + lastrow, 2).Value = Mid(.FoundFiles(i), x, Len(.FoundFiles(i)) -
x - 3)
Cells(i + lastrow, 3).Value = FileLen(.FoundFiles(i))
Cells(i + lastrow, 4).Value = FileDateTime(.FoundFiles(i))
Cells(i + lastrow, 5).Value = .FoundFiles(i) 'Path to play
End If 'added
Next i

Else
MsgBox "There were no files found."
End If
End With

Range("a5:g" & Range("a65536").End(xlUp).Row) _
..Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2),
Order2:=xlAscending, Orientation:=xlTopToBottom
[a5].Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

This one should do what you want. I tested recently.

 

[Dave Peterson]

I like this one, too from Debra Dalgleish's site:

http://contextures.com/excelfiles.html
look for:
UF0005 - Music Playlist Creator

It creates a folder on your desktop with a bunch of .m3u files (playlists) for
each folder in a user specified folder.

It can make playing your favorite music a little easier.

This is one of my own from a while back. Needs modernizing.
Here is one I have used in my "songs.xls". Written a couple of years ago.

Sub FindFiles()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'sets Erase or Append option
lastrow = Range("a65536").End(xlUp).Row
If lastrow = 4 Then lastrow = 5 Else lastrow = lastrow
'MsgBox lastrow
If UCase([a3]) = "E" Then
Range("a5:f" & lastrow).ClearContents
lastrow = 4
ElseIf UCase([a3]) = "A" Then lastrow = lastrow
End If
'Sets Musicpath
If Right([a1], 1) <> "\" And Left([a1], 1) <> "_" Then x = "\"
If IsEmpty([a2]) = False And Right([a2], 1) <> "\" Then y = "\"
musicpath = [a1] & x & [a2] & y
'Finds Files
With Application.FileSearch
.NewSearch
.LookIn = musicpath
.SearchSubFolders = True 'False
.MatchTextExactly = False
.Filename = ".mp3" '*.mp3* did not work in 97
If .Execute(msoSortOrderDescending) > 0 Then
'MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox Mid(.FoundFiles(i), Len(musicpath) + 1, 2)
If Mid(.FoundFiles(i), Len(musicpath) + 1, 2) <> "__" Then 'added for
__INCOMPLETE

x = Application.Find("\", StrReverse(.FoundFiles(i))) - 2 'must have
function before xl2000
y = Application.Find("-", StrReverse(.FoundFiles(i))) - 1

Cells(i + lastrow, 1).Value = Mid(.FoundFiles(i), Len(.FoundFiles(i)) - x,
x - y)
x = Application.Find("-", .FoundFiles(i)) + 1
Cells(i + lastrow, 2).Value = Mid(.FoundFiles(i), x, Len(.FoundFiles(i)) -
x - 3)
Cells(i + lastrow, 3).Value = FileLen(.FoundFiles(i))
Cells(i + lastrow, 4).Value = FileDateTime(.FoundFiles(i))
Cells(i + lastrow, 5).Value = .FoundFiles(i) 'Path to play
End If 'added
Next i

Else
MsgBox "There were no files found."
End If
End With

Range("a5:g" & Range("a65536").End(xlUp).Row) _
.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2),
Order2:=xlAscending, Orientation:=xlTopToBottom
[a5].Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub