Results 1 to 14 of 14
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    VBA code to retrieve duration of sound files

    Does anyone have a few lines of code that will retrieve the duration (item 21) from sound files? I found a few descriptions of Scripting but now how to obtain the durations in VBA. Not knowing "beans" about Script I might be missing something obvious to the trained eye?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    VB script should not be too hard to translated to VBA. If what you are after are part of the file properties, you should be able to do this by referencing the File System Object (Microsoft Office XX.X Object Library). However, I do not believe things like "duration of video" is part of that.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I finally found a website that zeroed in on the issue from a VBA point-of-view. http://stackoverflow.com/questions/5...ile-attributes

    From that sites example, I put together the following test code.

    Code:
    Option Compare Database
    Option Explicit
    Public Function GetDuration()
    Dim sFile As Variant
    Dim oShell: Set oShell = CreateObject("Shell.Application")
    Dim oDir:   Set oDir = oShell.Namespace("D:\WaveFiles\Angels Aware\")
    
    For Each sFile In oDir.Items
       Debug.Print oDir.GetDetailsOf(sFile, 27)
    Next
    
    End Function
    Turns out that it is item 27 that pertains to the duration of audio files. I tested successfully with both wav and mp3 files.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Nice work. Thanks for posting your solution.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Maybe I need to start a new post, but while the code posted earlier as a solution is correct in what it does, I can't figure out how to change the code so that I can pass a fully qualified file name to the function "Duration" and have the function return the duration value. As it is, the posted code returns a list of file names in the oDir object using the "Namespace" method. I'm flying blind because really don't know what method to use to set sFile myself. Any suggestions I can try?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I referenced Microsoft Shell Controls And Automation

    I created a function and called it using something like this ...
    Code:
    Dim strPath As String
    Dim strFilename As String
    strPath = "C:\Test\VideoFiles\"
    strFilename = "IntroVBA_Tutorial01.wmv"
    MsgBox GetMediaFileLength(strPath, strFilename)
    Here is the function
    Code:
    Public Function GetMediaFileLength(ByVal p_FolderPath As String, ByVal p_FileName As String) As String
    
        If p_FolderPath <= "" Or p_FileName <= "" Then
            MsgBox "Please provide a folder and a path."
        Exit Function
        End If
    
    GetMediaFileLength = "No File Found"
    Dim objShell As New Shell
    Dim objFolder As Folder3
    Dim objFile As FolderItem
    
    Set objFolder = objShell.NameSpace(p_FolderPath)
    
        For Each objFile In objFolder.Items
        
            If objFile.Name = p_FileName Then
                GetMediaFileLength = objFolder.GetDetailsOf(objFile, 27)
            End If
        
        Next objFile
    
    End Function

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I can't get your function code to compile. Apparently there's something in your reference that I don't have in A2003, as the compiler does not recognize "New Shell", "Folder3" nor "FolderItem".

    I tried to circumvent the issue by substituting:
    Code:
    Dim objShell: Set objShell = CreateObject("Shell.Application")
    Dim objfolder: Set objfolder = objShell.NameSpace(LibName)
    Dim objFile: Set objFile = Null
    The substitution compiled okay but as expected I got a type mismatch on the objFile set statement. The "For Each objFile in objfolder.Items" fails in execution if objFile is not set. Obviously, I don't know how to set it properly.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I don't know how the 1st "solution" executed successfully, as sFile is Variant NOT obj?

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Hmmm. I was able to use late binding on the folder and file objects and get it to work.
    Code:
    Dim objShell As New Shell
    Dim objFolder As Object
    Dim objFile As Object
    I am having trouble passing the folder path to a late bound Shell Object via a variable. Let me check some of my notes and see what I can see.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by GraeagleBill View Post
    I don't know how the 1st "solution" executed successfully, as sFile is Variant NOT obj?
    Variant is larger than Object. Variant will adapt to String, Integer, Object, etc.. Each one being a different sized Variant.

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Okay!!!! In reply to your #6, I was able to compile and successfully execute the following code AFTER adding "Microsoft Shell Controls and Automation" reference library.

    Code:
    Option Compare Database
    Option Explicit
    Private Sub tester()
    Dim strPath As String
    Dim strFilename As String
    strPath = "D:\WaveFiles\Angels Aware\"
    strFilename = "Side 2.mp3"
    MsgBox GetMediaFileLength(strPath, strFilename)
    End Sub
    Public Function GetMediaFileLength(ByVal p_FolderPath As String, ByVal p_FileName As String) As String
    
        If p_FolderPath <= "" Or p_FileName <= "" Then
            MsgBox "Please provide a folder and a path."
        Exit Function
        End If
    
    GetMediaFileLength = "No File Found"
    Dim objShell As New Shell
    Dim objFolder As Folder3
    Dim objFile As FolderItem
    
    Set objFolder = objShell.NameSpace(p_FolderPath)
    
        For Each objFile In objFolder.Items
        
            If objFile.Name = p_FileName Then
                GetMediaFileLength = objFolder.GetDetailsOf(objFile, 27)
            End If
        
        Next objFile
    
    End Function

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Oh, that's good. I was riding on the assumption you caught that.

    It's funny though. Now I am trying to figure out how to pass the string to the Shell argument with late binding.
    Code:
    Set oDir = oShell.Namespace(""" & p_FolderPath & """)
    Use the escape character in the variable and in the statement, itself is not helping. I even tried ByRef in the Function's parameters. Now I am bugging out on this. It has to be simple.

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    To your #9...............

    I too could not get the code to work with late binding of objFolder object. I've successfully run the code posted in my #11 and I've updated an app's table of sound files to include their playing time. There's a bit more development work to be done where the duration is captured as the sound files are tabled each week, but the function you've suggested will get the job done.

    Thanks for all your help,
    Bill

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Glad to hear. I was having fun trying to figure out how to pass the variable to the namespace. Then, it was not so much fun and I gave up. Now I am thinking external script. Maybe some other day ...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 02-24-2015, 10:43 AM
  2. Why code completion duration vary?
    By tezza79 in forum Modules
    Replies: 3
    Last Post: 02-23-2015, 04:50 PM
  3. Replies: 2
    Last Post: 10-05-2012, 07:52 AM
  4. Access - Automatical retrieve files
    By eric.kung in forum Access
    Replies: 8
    Last Post: 08-18-2011, 06:51 AM
  5. inserting .wav sound?
    By dada in forum Programming
    Replies: 2
    Last Post: 08-20-2010, 11:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums