|
|
DATA MANAGEMENT
Find Your MP3 Data via VBA Programming
Using MP3 files as an example, learn how you can search for data on your hard drive.
In my article in the December 2004 issue, "Explore Your MP3s," I showed you how you can read the information stored in a typical MP3 file. (Subscribers can access this article online at http://Advisor.com/doc/14849.) In this article, I'm going to show you how you can find all the MP3 files on your hard drive.
Find the files
What's the easiest way of finding all the MP3 files on your hard drive? I'm going to make some simplifying assumptions to begin. Assume all the MP3s are going to exist on a single drive, and that all you care about is files with a single extension (*.MP3).
There are three common approaches to enumerating files:
- Use the Dir function
- Use FileSystemObject (FSO) from the Scripting library
- Use the FindFirstFile, FindNextFile and FindClose APIs
I'm going to show you how to use each of the three different methods, then look briefly at the relative merits of each.
Using the VBA Dir function to find files
The Dir function returns a string that represents the name of a file or folder that matches a specified pattern or file attribute. You must specify a pathname the first time you call the Dir function, or an error occurs. (You can also specify file attributes, such as Hidden or System or Directory, as long as you've provided a pathname). Dir returns the first file name that matches pathname. To get any additional file names that match pathname, you call Dir again with no arguments.
Dir works fine to list all of the files in a single directory:
strFile = Dir$(strFolder & "*.mp3")
Do While Len(strFile) > 0
Debug.Print strFolder & strFile
strFile = Dir$
Loop
It's also simple to get a list of subfolders in a given folder:
strSubfolder = Dir$(strFolder, vbDirectory)
Do While Len(strSubfolder) > 0
If (strSubfolder <> ".") And _
(strSubfolder <> "..") Then
If (GetAttr(strFolder & strSubfolder) _
And vbDirectory) = vbDirectory Then
Debug.Print strFolder & strSubfolder
End If
End If
strSubfolder = Dir$
Loop
The problem is that after you start having to go recursive (because of the subfolders), Dir can become awkward to use. This is because you can't nest Dir calls. If you make another call specifying path details, Dir wipes out the results of the previous call. One approach is to store the subfolders in a collection or array so you know all of them, then process each of the subfolders after you've done that:
Public Sub FindMP3FilesUsingDir( _
StartDir As String, _
FileList As Collection _
)
Dim strFile As String
Dim strFolder As String
Dim strSubfolder As String
Dim varFolder As Variant
Dim colSubfolders As Collection
' Ensure the starting folder is
' correctly formatted.
strFolder = QualifyFolderPath(StartDir)
If Len(strFolder) > 0 Then
' Add each of the files in the folder
' to the Collection.
strFile = Dir$(strFolder & "*.mp3")
Do While Len(strFile) > 0
FileList.Add strFolder & strFile
strFile = Dir$
Loop
' Build a list of subfolders in the folder,
' adding each one to a local collection.
Set colSubfolders = New Collection
strSubfolder = Dir$(strFolder, vbDirectory)
Do While Len(strSubfolder) > 0
If (strSubfolder <> ".") And _
(strSubfolder <> "..") Then
If (GetAttr(strFolder & strSubfolder) _
And vbDirectory) = vbDirectory Then
strSubfolder = strFolder & strSubfolder
colSubfolders.Add strSubfolder
End If
End If
strSubfolder = Dir$
Loop
' Process each subfolder found above recursively.
' Note that you must use a variant when
' enumerating the elements of a collection,
' but our function is expecting a string.
For Each varFolder In colSubfolders
strFolder = varFolder
Call FindMP3FilesUsingDir(strFolder, FileList)
Next varFolder
End If
End Sub
The QualifyFolderPath method, referred to in FindMP3FilesUsingDir above, is simply a helper function I use to ensure the folder name ends in a slash:
Function QualifyFolderPath(PathName As String) _
As String
If Len(PathName) > 0 Then
If Right$(PathName, 1) <> "\" Then
QualifyFolderPath = PathName & "\"
Else
QualifyFolderPath = PathName
End If
Else
QualifyFolderPath = ""
End If
End Function
You call that routine using code similar to this procedure:
Public Sub dirTest()
Dim colFileList As Collection
Dim intLoop As Integer
Dim strStartDir As String
Dim strMessage As String
Dim varFile As Variant
strStartDir = "C:\My Music"
Set colFileList = New Collection
Call FindMP3FilesUsingDir( _
strStartDir, colFileList)
If colFileList.Count = 1 Then
strMessage = "There is 1 file under "
Else
strMessage = "There are " & _
colFileList.Count & " files under "
End If
strMessage = strMessage & strStartDir
Debug.Print strMessage
For Each varFile in colFileList
Debug.Print varFile
Next varFile
Debug.Print
Set colFileList = Nothing
End Sub
Using FileSystemObject to find files
The FileSystemObject (FSO) object model is intended to provide access to a computer's file system. The methods included as part of FSO are quite simple to use (especially when compared to using APIs, as you'll see in the next section!). One of the objects in FSO is the Folder object, which contains methods and properties that let you create, delete, or move folders, as well as let you query the system for folder names, paths, and various other properties. The Folder object contains a built-in collection of Folders, with each Folder in the collection containing a collection of Files. It’s more straightforward, therefore, to navigate through the file system than using the Dir function, but tests I've done indicate it's significantly slower.
To list all the files within a folder, you'd use something like this:
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(strFolder)
For Each objFile In objFolder.Files
Debug.Print strFolder & objFile.Name
Next objFile
Note the Files collection contains all files, regardless of extension. To only find the MP3 files in the folder, you have to check that the file type is "MPEG Layer 3 Audio" (or whatever the .MP3 extension is linked to on your system) or that the last four characters of the file name is .MP3. Or you can use the PathMatchSpec API, which searches a string using a Microsoft MS-DOS wild card match type:
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(strFolder)
For Each objFile In objFolder.Files
If objFile.Type = "MPEG Layer 3 Audio" Then
Debug.Print strFolder & objFile.Name
End If
Next objFile
Or:
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(strFolder)
For Each objFile In objFolder.Files
If Right$(objFile.Type, 4) = ".mp3" Then
Debug.Print strFolder & objFile.Name
End If
Next objFile
Or:
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(strFolder)
For Each objFile In objFolder.Files
If PathMatchSpec(StrPtr(objFile.Name), _
StrPtr("*.mp3)) Then
Debug.Print strFolder & objFile.Name
End If
Next objFile
Where you've included this declaration outside your routine:
Private Declare Function PathMatchSpec _
Lib "shlwapi" Alias "PathMatchSpecW" ( _
ByVal pszFileParam As Long, _
ByVal pszSpec As Long _
) As Long
The PathMatchSpec API has an added advantage (which I won't use in this article) that it can actually check for multiple file extensions. For example, if you want to find .MP3, .WAV and .WMA files, the call to PathMatchSpec would be:
If PathMatchSpec(StrPtr(objFile.Name), _
StrPtr("*.mp3, *.wav, *.wma")) Then
Debug.Print strFolder & objFile.Name
End If
(For the curious, StrPtr is an undocumented function contained within VBA, often required when using API calls. Most API calls are written in C, which uses pointers to strings, as opposed to strings. Some API calls that expect strings can get the pointer to the string directly, but others, such as the PathMatchSpec function, essentially expect a pointer to a pointer. The StrPtr function provides the address of the pointer to the string, which is what the API requires in this case. And don't worry if you don't understand that!)
You can use this code to replace the previously shown FindMP3FilesUsingDir procedure:
Public Sub FindMP3FilesUsingFSO( _
StartDir As String, _
FileList As Collection _
)
Dim objFSO As Object ' FileSystemObject
Dim objFile As Object ' File
Dim objFolder As Object ' Folder
Dim objRootFolder As Object ' Folder
Set objFSO = _
CreateObject("Scripting.FileSystemObject")
Set objRootFolder = objFSO.GetFolder(StartDir)
For Each objFile In objRootFolder.Files
With objFile
If MatchSpec(.Name, ".mp3") Then
FileList.Add _
objRootFolder.Path & "\" & .Name
End If
End With
Next objFile
For Each objFolder In objRootFolder.SubFolders
With objFolder
Call FindMP3FilesUsingFSO( _
objRootFolder.Path & "\" & .Name, _
FileList _
)
End With
Next objFolder
End Sub
MatchSpec is another helper function I use to determine whether I'm interested in a particular file:
Private Function MatchSpec( _
FileName As String, _
FileSpec As String _
) As Boolean
MatchSpec = PathMatchSpec(StrPtr(FileName), _
StrPtr(FileSpec))
End Function
Note I'm using late binding in FindMP3FilesUsingFSO. That means you don't have to add any additional references to the application, and it minimizes the risk of reference incompatibilities when you distribute your application to others. If, however, you prefer using early binding, you can add a reference to the Microsoft Scripting Runtime library. This is the file scrrun.dll, which should be found in your System32 folder. After you've added this reference, use the following code instead of what I've got above:
Public Sub FindMP3FilesUsingFSO( _
StartDir As String, _
FileList As Collection _
)
Dim objFSO As FileSystemObject
Dim objFile As File
Dim objFolder As Folder
Dim objRootFolder As Folder
Set objFSO = New FileSystemObject
Set objRootFolder = objFSO.GetFolder(StartDir)
For Each objFile In objRootFolder.Files
With objFile
If MatchSpec(.Name, ".mp3") Then
FileList.Add _
objRootFolder.Path & "\" & .Name
End If
End With
Next objFile
For Each objFolder In objRootFolder.SubFolders
With objFolder
Call FindMP3FilesUsingFSO( _
objRootFolder.Path & "\" & .Name, _
FileList _
)
End With
Next objFolder
End Sub
Using API calls to find files
Finally, let's discuss how to use the FindFirstFile, FindNextFile and FindClose APIs to search for files.
The FindFirstFile function opens a search handle and returns information about the first file whose name matches the specified pattern. (It searches both the long and short file names.) After you've established the search handle, you can use the FindNextFile function to search for other files that match the same pattern. When you no longer need the search handle, you can close it using the FindClose function.
Before you can use this technique, you have to declare all the constants, types and functions you'll need to use:
Private Const ALL_FILES = "*.*"
Private Const INVALID_HANDLE_VALUE = -1
Private Const MAX_PATH = 260
Private Const vbBackslash = "\"
Private Const vbDot = 46
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type
Private Declare Function FindClose _
Lib "kernel32" ( _
ByVal hFindFile As Long _
) As Long
Private Declare Function FindFirstFile _
Lib "kernel32" Alias "FindFirstFileA" ( _
ByVal lpFileName As String, _
lpFindFileData As WIN32_FIND_DATA _
) As Long
Private Declare Function FindNextFile _
Lib "kernel32" Alias "FindNextFileA" ( _
ByVal hFindFile As Long, _
lpFindFileData As WIN32_FIND_DATA _
) As Long
This procedure provides the same functionality as the previously shown FindMP3FilesUsingDir and FindMP3FilesUsingFSO procedures:
Public Sub FindFindMP3FilesUsingAPI( _
StartDir As String, _
FileList As Collection _
)
Dim lngFileHandle As Long
Dim strFile As String
Dim strFolder As String
Dim typWFD As WIN32_FIND_DATA
strFolder = QualifyFolderPath(StartDir)
lngFileHandle = FindFirstFile( _
strFolder & ALL_FILES, typWFD)
If lngFileHandle <> INVALID_HANDLE_VALUE Then
Do
With typWFD
' If we've found a subfolder, call the routine
' again, passing it the subfolder.
If (.dwFileAttributes And vbDirectory) _
Then
If Asc(.cFileName) <> vbDot Then
Call FindMP3FilesUsingAPI ( _
strFolder & TrimNull(.cFileName), _
FileList)
End If
Else
' If it's not a folder, it must be a file.
' If it matches the specifications, add it
' to the collection
If MatchSpec(.cFileName, "*.mp3") Then
FileList.Add _
strFolder & TrimNull(.cFileName)
End If
End If
End With
' Get the next file
Loop While _
FindNextFile(lngFileHandle, typWFD) <> 0
End If
End Sub
TrimNull is the final helper function I require in this section. When dealing with APIs, strings are normally terminated with a Null character (Chr$(0)). TrimNull simply returns everything in the string up to the first Null character:
Private Function TrimNull( _
InputString As String _
) As String
TrimNull = Left$(InputString, _
InStr(InputString, Chr$(0)) - 1)
End Function
Wrap up
Okay, so I've shown you three different methods of achieving the same results (four, if you want to count Late Binding and Early Binding as separate methods). The first one used the built-in VBA Dir function (as well as a VBA collection) to find all the files. I then showed two different ways to use the FileSystemObject model. Finally, I showed how to use three different API functions (FindFirstFile, FindNestFile and FindClose). What's the difference, and which one should you use?
I hate to say it, but I can't help you decide: That's up to you.
It strikes me that the Dir approach is the easiest to understand, and it uses only built-in VBA methods.
FSO is perhaps a little more elegant and object-oriented, if that matters to you. However, as I said earlier, my testing shows it's significantly slower (and late binding vs. early binding doesn't seem to matter).
I have to confess I have a weak spot for using APIs, though it strikes me they should be faster (after all, they're closer to the operating system itself).
I've mentioned speed several times, so let's see just what kind of difference there is. I've created a small test database to accompany this article. It searches the same folder structure using the different approaches, and reports on how long each takes. Running it on my Dell Inspiron shows just what kind of difference there is. The results of running it on my Dell Inspiron 5150 (which has a Pentium 4 2.66 GHz processor with 512 MB of RAM) are shown in figure 1.
As you can see, in finding 5491 files on my hard drive, the API approach was about 10 times faster than the Dir function (.191 seconds vs. 1.222 seconds), while the Dir function was about 10 times faster than the FSO approaches (1.222 seconds vs. 10.285 or 10.295 seconds). When I modified the routines to look for more than simply MP3 files, this relationship was repeated.
In the end, you're the only one who can decide whether the 10 second savings is worth implementing the API code.
In my next article, I'll take a look at tying together the tag parsing routine from the first article (December 2004)and the file searching techniques in this article to actually load all the data into an Access database.
Doug Steele has worked for many years with databases on mainframes and PCs. He has been recognized as an Access MVP for his contributions to Microsoft-sponsored newsgroups. http://I.Am/DougSteele AccessHelp@rogers.com
ARTICLE INFO
Web Edition: 2005 Week 15, Doc #16279
 FREE ACCESS
Keyword Tags: Data Acquisition, Database, Database Development, Database Management, Microsoft, Microsoft Access, Microsoft Office, Microsoft Office Access, Security, VBA
ADVISORAMA For the skeptic there remains only one consolation: If there should be such a thing as superhuman law it is administered with subhuman inefficiency. -- Eric Ambler |
|