Song file titles into Excel?

R

Rob McLean

I have many MP3s on my hard drive, and I'd like to download the filenames
directly into Excel. Is there a quick way to do this? Or would I have to
copy-and-paste them one at a time?
 
D

Don Guillett

try this
Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "c:\yourfolder\*.mp3"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub
 
J

Jay

I have many MP3s on my hard drive, and I'd like to download the filenames
directly into Excel. Is there a quick way to do this? Or would I have to
copy-and-paste them one at a time?
Click to expand...

If they're in one folder, one way to do this in Win98 is to use a batch
file like this:

@dir %1 /-p /o:gen > "%temp%\dir.txt"
notepad "%temp%\dir.txt"
del "%temp%\dir.txt"
exit

Then add a menu command to folders, pointing it to the batch file.

With this setup, I can right-click on a folder, invoke the menu command,
and get a text file with the list of filenames.

After cleaning it up a bit, the text file can imported by Excel.

If you want a different-looking list, look into switches for the DOS "dir"
command to get variations. For example, see:
http://home7.inet.tele.dk/batfiles/msdos7/

If they aren't in one folder, you might set up a folder using Windows
"Find" looking for ".mp3". Select the entire list (control-A), drag with
the right-mouse-button to a newly created folder with "Copy shortcuts
here". Then use the method above to get the list.
 
A

Arvi Laanemets

Hi

This UDF returns name of n-th file with given extension in given folder.

Public Function GetMyFile(MyFolder As String, FileNum As Integer,
MyExtension As String)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set fc = f.Files
i = 0
For Each f1 In fc
If Right(f1.Name, 3) = MyExtension Then
i = i + 1
If i = FileNum Then GetMyFile = f1.Name
End If
Next
End Function

P.e. into cell A1 enter the formula like this:
=IF(ISERROR(GetMyFile("C:\My Music\",ROW(),"mp3")),"",GetMyFile("C:\My
Music\",ROW(),"mp3"))

and copy it down


Arvi Laanemets
 
S

SeattleExcel

...or if you don't want to mess with much code, you could open Dos
- Start/Run/CMD .. gets your to the command prompt
- CD to the right directory
- Dir > MyFiles.txt .. this will pipe the file names into a text file

You could then open the text file (notepad), then copy/past the
files into a worksheet. You could then "Data/Text to Columns"
to get the file names seperated out.

If you regulary need this functionality, I'd play around with the VBA
code a bit, or request the add-in.

(..am I just dogging my own solution? ..haha..)

Cheers!
 
C

CLR

I've used Jim Cone's "ListFiles" add-in many times for things just such as
this.........it works super-fine.

Vaya con Dios,
Chuck, CABGx3
 
S

Sunil Jayakumar

If you are using itunes, there is an export Library option, which will give
you all the ID3 tags as well.
this is an XML file, and is quite useful if you use a portable hard drive on
various PC's, as you don't need to let it build the whole list again. It
exports in XML format, which you can then open in Excel.

Hope this helps

Sunil Jayakumar


CLR said:
I've used Jim Cone's "ListFiles" add-in many times for things just such as
this.........it works super-fine.

Vaya con Dios,
Chuck, CABGx3
Click to expand...
www.ayyoo.com/credit-cards.html
 
Top