Import directory data into Excel 2003

  • Thread starter Thread starter Nigel Reed
  • Start date Start date
N

Nigel Reed

I have over 1000 media files that I would like to extract information
from and put into an Excel spreadsheet.

Using Explorer, I have defined the fields I would like to see, such as
title, duration, comment etc. Now, I need to import this data into
Excel.

So far, I've not been able to find a way to do this. Can someone offer
some suggestions please?

Thanks,
Nigel

--
www.myoldcontacts.com - Tell your friends to tell their friends
www.sysadmininc.com - Consultancy, Service, Sales, Networking...
www.british-expats.com - Connect with British Expats World Wide
www.kxez.com/shows_britishinvasion.php - 9-11pm Sunday. KXEZ 92.1 FM


"I reject your reality and substitute it with my own" --Adam Savage.
 
John Walkenbach is busily copying his cds to mp3s and just posted a quick
and easy routine that puts everything in pivot tables. his code is available
on his blog a couple of days ago at www.j-walkblog.com. You would have to
tweak the code a bit to adjust for whatever file structure you are using.


Robin Hammond
www.enhanceddatasystems.com
 
Nigel

Several methods to accomplish this ike Tushar's best.

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. 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 Excel MVP
 
Robin Hammond said:
John Walkenbach is busily copying his cds to mp3s and just posted a quick
and easy routine that puts everything in pivot tables. his code is available
on his blog a couple of days ago at www.j-walkblog.com. You would have to
tweak the code a bit to adjust for whatever file structure you are using.
Click to expand...

Well spotted. Thanks. I'll take a look.

Regards
Nigel
--
www.myoldcontacts.com - Tell your friends to tell their friends
www.sysadmininc.com - Consultancy, Service, Sales, Networking...
www.british-expats.com - Connect with British Expats World Wide
www.kxez.com/shows_britishinvasion.php - 9-11pm Sunday. KXEZ 92.1 FM


"I reject your reality and substitute it with my own" --Adam Savage.
 
Gord Dibben said:
Nigel

Several methods to accomplish this ike Tushar's best.

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

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

This isn't going to do the job since it doesn't give you any details
about the file such as bit rate, duration etc.
OR Go to DOS(Command) prompt and directory.
Type DIR >MYFILES.TXT
Likewise.

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

Likewise. Therefore I'm going to assume the freeware you suggested won't
do that either. I'll look at the other solution that was given and see
if that'll do the trick. Thanks anyway.

Regards
Nigel

--
www.myoldcontacts.com - Tell your friends to tell their friends
www.sysadmininc.com - Consultancy, Service, Sales, Networking...
www.british-expats.com - Connect with British Expats World Wide
www.kxez.com/shows_britishinvasion.php - 9-11pm Sunday. KXEZ 92.1 FM


"I reject your reality and substitute it with my own" --Adam Savage.
 
Here is one I created that assumes that ALL files are in exactly this format

artist name - song title.mp3

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
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
========
I then have a double_click event where I can play by double clicking on any
column.
 
Nigel,

While on the subject of scanning MP3s, here's a natty little one I knocked
up a couple of years ago that will read the ID3 tag info from an MP3 file
that you might find useful. Quite often, you'll find that the tag has better
info than the file name.

Sub Test()
Dim strSong As String
Dim strArtist As String
Dim strPath As String

strPath = "C:\Sounds Sort\Norah Jones - Come September.mp3"
GetID3TagInfo strPath, strSong, strArtist
Cells(1, 1).Value = strArtist
Cells(1, 2).Value = strSong
End Sub

Sub GetID3TagInfo(strPath As String, ByRef strSong As String, _
ByRef strArtist As String)
'assumes strPath contains a valid path to an mp3 that is not zero length
Dim hFILE As Integer
Dim strData As String * 128

hFILE = FreeFile
Open strPath For Binary Access Read Write As hFILE
Get hFILE, LOF(hFILE) - 127, strData
Close hFILE

If Left(strData, 3) <> "TAG" Then
strArtist = ""
strSong = ""
Else
strSong = Mid(strData, 4, 30)
Do While Right(strSong, 1) = " " Or Right(strSong, 1) = Chr(0)
strSong = Left(strSong, Len(strSong) - 1)
Loop
strArtist = Mid(strData, 34, 30)
Do While Right(strArtist, 1) = " " Or Right(strArtist, 1) = Chr(0)
strArtist = Left(strArtist, Len(strArtist) - 1)
Loop
End If
End Sub

Robin Hammond
www.enhanceddatasystems.com
 
Back
Top