Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a sub in Access, posted below, which uses the Microsoft Scripting Runtime library, to determine files properties but, using Intellisense to see the list of all the properties available in VBA for Scripting.File, I don't see a Length(hour, minutes, seconds) property, there's only a Size property. But I know that if, in Windows Explorer, you hover the mouse over an audio file name, it also shows its time length, is there a way to get that in VBA?
    Code:
    Public Sub GetFilesNamesFromFolder(strFolderPath As String)
    
    On Error GoTo ErrorHandler
    
       Dim fso As Scripting.FileSystemObject
       Dim fld As Scripting.Folder
       Dim fil As Scripting.File
       Dim strSQL As String
       Dim dbs As DAO.Database
       Dim rst As DAO.Recordset
       Dim strPrompt As String
       Dim strTitle As String
       Dim strTable As String
       Dim intMissingCount As Integer
       
    
       strTable = "tblFilesFromFolder"
       Set fso = CreateObject("Scripting.FileSystemObject")
       If Not fso.FolderExists(strFolderPath) Then
    	  'Specified folder does not exist
    	  strPrompt = "Please enter a valid folder path"
    	  strTitle = "Folder path not found"
    	  MsgBox strPrompt, vbCritical + vbOKOnly, strTitle
    	  GoTo ErrorHandlerExit
    	  Forms![fdlgSelectFolder].SetFocus
       Else
    	  'Folder exists
    	  Set dbs = CurrentDb
    	  Set rst = dbs.OpenRecordset(strTable)
    	  Set fld = fso.GetFolder(strFolderPath)
    	  For Each fil In fld.Files
    		 If fil.Name = "DSSMANAGEMENT.Dat" Then GoTo mm
    		 rst.AddNew
    		 rst![FileName] = fil.Name
    		 If Forms!fdlgSelectFolder!m Then
    		 rst![DateChecked] = fil.DateLastModified
    		 Else
    		 rst![DateChecked] = fil.DateCreated
    		 End If
    		 rst.Update
    mm:
    	  Next fil
       End If
       
    
       
    ErrorHandlerExit:
       Exit Sub
    
    ErrorHandler:
    If Not Err = 3022 Then
    
       MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
     
       Else: MsgBox "You already have all or some of the files you want to import."
       End If
       Resume ErrorHandlerExit
    
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Retrieving Extended File Properties.
    If you'd like to view a working example, see John Walkenbach's Creating A List of MP3 Files. It's for Excel but the code can be adapted for Access.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans, I've run the Sub DisplayMP3Info() included in the Excel file and it works superbly, listing all the MP3 files contained in a folder, is there any way to modify it so that it could list other types as well, such as WMA or WAV?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    John Walkenbach's DisplayMP3Info procedure has a line

    If Right(.FoundFiles(i), 3) = "mp3" Then

    You can change this to

    If Right(.FoundFiles(i), 3) = "mp3" Or Right(.FoundFiles(i), 3) = "wma" Or Right(.FoundFiles(i), 3) = "wav" Then

    If you have a folder that contains only sound files, you could even remove the If ... Then line and the corresponding End If.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans, unfortunately that modification works for detecting wma's duration but not for detecting wav files duration; the cells corresponding to wav files duration in the Excel procedure output remain empty. Any workaround available?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have thousansds of .wav files on my PC, but the duration isn't listed in the file properties for any of them, only the bit rate. So I guess that this information simply isn't available for .wav files.

  7. #7
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Because the "bit rate" doesn't vary for .WAV files, I believe you can determine the length of a .WAV file from its size by using what I assume is a pretty simple formula. (I believe there's a set amount of space at the very start of a .WAV file that's reserved for certain information, and then the rest of the file would simply have so many bytes/second.) I don't know what the exact formula is, but I suspect you can find it pretty easily with some internet searching.

    Assuming you can, your workaround would simply be to have your code do its own calculation for the .WAV files, rather than getting the info from the file's properties.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you all for your timely and extremely helfpul input
    For the sake of completion, here's where you can find the formula sought after.

  9. #9
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Oops. The link you posted reminded me that .WAV files theoretically can have different sample rates, so it's not quite as simple as I said. However, assuming you're talking about a typical 2-channel .WAV file ripped from (or ready to be burned to) a CD, you can basically just divide the file size (in bytes) by 176,400.

    As further explanation (in case anyone ever reading this post is trying to figure out how or why this works), I note the following:

    1. I think a slightly more accurate formula would actually deduct the header size from the file size before dividing, but since the header is only 43 bytes long (assuming the link you posted is accurate), ignoring that is unlikely to affect the result (in seconds).

    2. My simple divide-by-176,400 formula assumes a sample rate of 44,100 samples per second (standard CD audio). The 44,100 gets multiplied by 2 twice -- first because there are 2 bytes per sample, and second because there are 2 channels (left and right).

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a wav file which is 274896 bytes and has a bit rate of 13kbps, what would its (even approximate)duration be?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='grovelli' post='771329' date='20-Apr-2009 09:10']I have a wav file which is 274896 bytes and has a bit rate of 13kbps, what would its (even approximate)duration be?[/quote]
    You can use the code from the site that you referred to yourself to find out...

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The sub at that site gives incorrect results, the wav file I attach here is 2 minutes 49 seconds long but the sub at the site gives
    0.528630769230769 seconds instead, while Windows Media Player gets the length right. I wonder how they do it...

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I suspect that this is some kind of compressed audio format disguised as a .wav.
    You can calculate the length by dividing the file size in bytes by the number of bytes per second - the code from your link retrieves this from the file header too.

    274888 bytes / 1625 bytes per second = 169.16 seconds = 2:49.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect. Thank you Hans

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='771190' date='18-Apr-2009 20:44']John Walkenbach's DisplayMP3Info procedure has a line

    If Right(.FoundFiles(i), 3) = "mp3" Then

    You can change this to

    If Right(.FoundFiles(i), 3) = "mp3" Or Right(.FoundFiles(i), 3) = "wma" Or Right(.FoundFiles(i), 3) = "wav" Then

    If you have a folder that contains only sound files, you could even remove the If ... Then line and the corresponding End If.[/quote]
    The FileLen function in the attached mdb uses the GetMP3TagInfo function from John Walkenbach's Creating A List of MP3 Files and it works perfectly, returning the file length of WMA and MP3 files. I've tried it yesterday on a pc running Access 2003 on Vista and I get FileLen = "" rather than a string specifying the file length. What gives??!! Can anyone try that out and confirm or disprove my finding?
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •