Does anyone have a spreadsheet that can . . .

[Russell]

Does anyone have a spreadsheet that can make a list of all the songs in my
mp3 music folder? I would love to have it emailed to me, if you have one
post here that you have and I will send you my email address.
Thanks
Russ

 

[Don Guillett]

It's easy to make a list using FileSearch. From vba HELP
With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With==========This modification should do itSub GetFileList()
Dim iCtr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\yourfolder"
' .SearchSubFolders = True
.Filename = ".mp3"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

======
Mine are laid out as
Carolyn Hester - House Of The Rising Sun.mp3

It depends on how the songs are in your folder as to how they show up. I can
custom design one for you that will list the songs by title and by artist.
You can sort on either and double click to play the song. You can also clear
duplicates, rename files, move files, kill files, copy files marked with a X
to another folder from which to make a cd, etc

 

[wiseman]

Russell,
I have seen several macros which can copy the file names from a directory
and put them in excel files, you have to change the directory in the macro
each time, Don Guillett has posted such a macro.

I found a word macro, which I prefer using as it prompts for the directory,
the list can then be copied to excel from word.
http://www.mvps.org/word/FAQs/General/PrintDocList.htm
Click on the bottom line 'See also: How to get the names of all the folders
in the folder tree, starting from a specified folder.' for the macro or use
the other methods just to print.

Barbara.

 

[Bob Phillips]

Russ,

This bit of code prints out all Excel files in a workbook starting at a
given folder. You should be able to adapt to suit.

Option Explicit

Dim oFSO
Dim cfiles As Long
Dim iRow As Long
Dim iLevel As Long


Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")
iRow = 1
iLevel = 1
selectFiles oFSO.GetFolder("C:\MyTest")

End Sub

Sub selectFiles(fldr As Object)

Dim oSubFolder As Object
Dim oFile As Object
Dim WB As Workbook


With Cells(iRow, iLevel)
.Value = fldr.Name
.Font.Bold = True
End With
iRow = iRow + 1

For Each oFile In fldr.Files
If Right(oFile.Name, 4) = ".xls" Then
Cells(iRow, iLevel) = oFile.Name
iRow = iRow + 1
End If
Next oFile

iLevel = iLevel + 1

For Each oSubFolder In fldr.SubFolders
selectFiles oSubFolder
Next oSubFolder

iLevel = iLevel - 1

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jim Cone]

Russ,
If you don't want to write code, I've already got it done for you in my
Excel add-in "List Files".
It finds files on your computer meeting criteria you specify. It will
generate a list on a new worksheet showing...

file location
name
type
size
last saved date

A hyperlink is created for each file.
A button is provide to delete the hyperlinks.
Comes with one page Word.doc instructions.
It is available, for free, upon direct request. Remove xxx from email
address.

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)
'-----------------------------------------------------

 

[Gord Dibben]

Russell

Several methods to accomplish this.......

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 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.

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 Excel MVP

 

[Don Guillett]

Thanks for the mention. The one I actually use in my program does prompt for
a directory typed in a cell. It could just as easily use an inputbox to ask
for a directory and/or sub folders.

 

[Ken Macksey]

Hi

I have one that I created that uses the free Tagwriter ocx and shows the
artist, title, Album,Comments,Genere and file location sorted by Artist and
song title.

Ken