Exporting audio file names and lengths to excel

Hawrylak

New Member
Joined
Dec 16, 2011
Messages
4
I am new to this form and in no way a programmer. It has been about 10 years since I have programmed anything so needless to say I need some help. I have a folder of audio files (.wav and or .mp3) and I would like to create an exel spreadsheet with a list of all the file names. I am able to do this now without a problem. The issue I am dealing with is I also need the length attribute associated with the aduio file in the spreadsheet as well. Ultimately what I need is a spreadsheet that has the file name in one column and the file length in another. Again, I am not familiar with VB so any help that can be provided would be greatly appreaciated. Thank you.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to the Forum,

The suggested link needs use to use Excel 2003 or before. If your version is different like 2007 or 2010 you would need to edit the VBA rather extensively. Shown below is a method to get the music list in with some of the attributes.

You will need to go into your VBA screen (Alt + F11) then use the Tools Menu and Select References and search down for Microsoft Scripting and click the box, then place this code in.

Sub TestListFilesInFolder()
Sheets("Sheet4").Activate 'Change sheet name
' add headers
With Range("A1")
.Formula = "Folder contents:"
.Font.Bold = True
.Font.Size = 12
End With
Range("A3").Formula = "File Name:"
Range("B3").Formula = "File Size:"
Range("C3").Formula = "File Type:"
Range("D3").Formula = "Date Created:"
Range("E3").Formula = "Date Last Accessed:"
Range("F3").Formula = "Date Last Modified:"
Range("G3").Formula = "Attributes:"
Range("H3").Formula = "Short File Name:"
Range("A3:H3").Font.Bold = True
ListFilesInFolder "C:\Users\Trevor Glovwer\Music\christian music\", True
' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Path & FileItem.Name
Cells(r, 2).Formula = FileItem.Size
Cells(r, 3).Formula = FileItem.Type
Cells(r, 4).Formula = FileItem.DateCreated
Cells(r, 5).Formula = FileItem.DateLastAccessed
Cells(r, 6).Formula = FileItem.DateLastModified
Cells(r, 7).Formula = FileItem.Attributes
Cells(r, 8).Formula = FileItem.Length
' use file methods (not proper in this example)
' FileItem.Copy "C:\FolderName\Filename.txt", True
' FileItem.Move "C:\FolderName\Filename.txt"
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Columns("A:H").AutoFit
' Set FileItem = Nothing
' Set SourceFolder = Nothing
' Set FSO = Nothing
' ActiveWorkbook.Saved = True
End Sub
 
Upvote 0
Thx a bunch for the code! I was looking for something like this as I need to make an excel with both filename and length of audio files allocated on a specific folder.

However this code retrieves me the following error on the line "Cells(r, 8).Formula = FileItem.Length":

Compile error: Method or date member not found

Any ideas about how to solve this?

Thx in advance! :)
 
Upvote 0
You more than likely will have files that are images etc in the folder location, comment out that line and it will run, then if you look to remove all the non music files including hidden files and uncomment that line it should work for you.
 
Upvote 0
Hey, thx for the reply :)

Well, not really, I just double checked that there are just .wav files on the specified folder, nothing else. But the code still retrieves the aforementioned issue.
And yes, if I comment out the line it works, but the value I was really looking for is the length :/

I must say that I'm completely newb about code programming, but surfing on the net I was able to found some code that in fact retrieves the audio length into a cell:


Code:
Private Declare PtrSafe Function mciSendString Lib "winmm" Alias "mciSendStringA" (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long     Dim FileName As String     Private Sub Form_Load()     
Dim RetString As String * 256         
FileName = "D:\Audio\voice_dialing.wav"           

mciSendString "Open " & FileName & " alias SoundFile", vbNullString, 0, 0&         
mciSendString "Set SoundFile time format milliseconds", vbNullString, 0, 0&         
mciSendString "Status SoundFile length", RetString, Len(RetString), 0&         
Range("B1") = (RetString) / 1000         
mciSendString "Close SoundFile", vbNullString, 0, 0&     End Sub

Pity thing is that it only works for one audio file, and I would like to show the lenght of any audio files inside a folder.

If you have any suggestions or tips about this, I would really appreciate it! :)
 
Last edited:
Upvote 0
Sorry, the right code is this one:

Code:
Private Declare PtrSafe Function mciSendString Lib "winmm" Alias "mciSendStringA" (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long
    Dim FileName As String
    Private Sub Form_Load()
    Dim RetString As String * 256
        FileName = "D:\Audio\voice_dialing.wav"


        mciSendString "Open " & FileName & " alias SoundFile", vbNullString, 0, 0&
        mciSendString "Set SoundFile time format milliseconds", vbNullString, 0, 0&
        mciSendString "Status SoundFile length", RetString, Len(RetString), 0&
        Range("B1") = (RetString) / 1000
        mciSendString "Close SoundFile", vbNullString, 0, 0&
    End Sub
 
Upvote 0
I just used the code you posted and it worked really good for me with a few tweaks.
I know that this is an old post but I thought I would respond to your question about finding the time for each .wav file in a folder since that is what I needed it for.
It could be done several ways but here is one.
Code:
Private Declare Function mciSendString Lib "winmm" Alias "mciSendStringA" & _
(ByVal lpstrCommand As String, ByVal lpstrReturnString As String, & _
ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long
Public FileName as String
Public FileTime as String

Sub LoopTheWavs
Dim TrgtFolder As String
Dim f as String
TrgtFolder = "C:\Audio\"
Cells(1, 1) = "File Name"
Cells(1, 2) = "Audio Length"
r = 2
f = Dir(TrgtFolder & "*.wav")
Do While f <> ""
Cells(r, 1) = f
FileName = TrgtFolder & f
Form_Load
Cells(r, 2) = Round(FileTime, 3)
f = Dir
Loop
End Sub

Private Sub Form_Load()
Dim RetString As String * 256         
mciSendString "Open " & FileName & " alias SoundFile", vbNullString, 0, 0&         
mciSendString "Set SoundFile time format milliseconds", vbNullString, 0, 0&         
mciSendString "Status SoundFile length", RetString, Len(RetString), 0&         
FileTime = (RetString) / 1000         
mciSendString "Close SoundFile", vbNullString, 0, 0&     
End Sub

I wrote this in this window so it might need to be adjusted, but I think I got the basics right. Don't know if you even need the answer any more but here it is.

Anyway, Thanks for my answer!
 
Upvote 0
Hey MPW, thx a lot for your contribution. I finally found a way to do it but I do really appreciate your help and interest :)
I'll share my code as well in case it might be helpful for others!

Code:
Declare PtrSafe Function mciSendString Lib "winmm" Alias "mciSendStringA" _
(ByVal lpstrCommand As String, ByVal lpstrReturnString As String, _
ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long
Sub Form_LoadData()
    Dim RetString As String * 256
    Dim filename As String
    Dim iCTR As Long  
    Dim bits As String * 256
    Dim Hz As String * 255

        For iCTR = 1 To 2000  
        filename = Range("I" & iCTR).Value  
        On Error Resume Next  
        
        mciSendString "Open " & Range("B7").Value & "\" & filename & " alias SoundFile", vbNullString, 0, 0&
        mciSendString "Set SoundFile time format milliseconds", vbNullString, 0, 0&
        mciSendString "Status SoundFile length", RetString, Len(RetString), 0&
        mciSendString "Status Soundfile bitspersample", bits, 256, 0&
        mciSendString "Status Soundfile bytespersec", Hz, 256, 0&

        Range("M" & iCTR) = Str(Hz) / 2
        Range("L" & iCTR) = Str(bits)
        Range("K" & iCTR) = (RetString) / 1000 'IMPROVEMENT
        mciSendString "Close SoundFile", vbNullString, 0, 0&
    Next iCTR  
    

        For iCTR = 1 To 2000  
        filename = Range("H" & iCTR).Value  

        mciSendString "Open " & Range("B9").Value & "\" & filename & " alias SoundFile", vbNullString, 0, 0&  
        mciSendString "Set SoundFile time format milliseconds", vbNullString, 0, 0&
        mciSendString "Status SoundFile length", RetString, Len(RetString), 0&
        mciSendString "Status Soundfile bitspersample", bits, 256, 0&
        mciSendString "Status Soundfile bytespersec", Hz, 256, 0&

        Range("L" & iCTR) = Str(Hz) & " Hz"
        Range("K" & iCTR) = Str(bits) & " Bits"
        Range("J" & iCTR) = (RetString) / 1000  
       mciSendString "Close SoundFile", vbNullString, 0, 0&
    Next iCTR  
End Sub

The code only works if you have already the audio filenames displayed in some specific columns. So with the aid of another script you display the audio files allocated in a path (cells B7 and B9).
Besides the audio duration it also displays the Hz and bits.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top