How to Retrieve Extended File Properties (1 Viewer)

Steve R. · Mar 6, 2012

I've been trying to figure out how to extract extended file properties in Access. Apparently the FileSystem object only returns the standard set of file properties, such as the name of the file. Microsoft has a description of how to use VBA to retrieve extended file properties, such as the author of a file.

Additionally, stngman posted in UtterAccess an approach on how to retrieve extended file properties in Access.

The approach used by stngman, while it works, appears to require a lot of looping. I was hoping to avoid this looping since my program is already looping through the files. Alas, all that I have been able to get is the name of the extended property NOT the actual value. See the line of code in bold red.

Based on the Microsoft table, the file property with the value of "0" should return the actual name of the file, but all I get is the the name of the field which is "name". Any Thoughts?

Code:

Public Sub Create_File_List(InputPath As String)
    Dim CopyMP3RS As DAO.Recordset
    Dim Fso As Object
    Dim Fldr As Object
    Dim SubFldr As Object
    Dim F As Object
    Rem New Shell.Application Code Below -----------------------------------
    Dim objShell As Object
    Dim ojbFolder As Object   
    Set objShell = CreateObject("Shell.Application")
    Rem Rem New Code Above --------------------------------
    Set Fso = CreateObject("scripting.filesystemobject")
    Set Fldr = Fso.GetFolder(InputPath)
    Set CopyMP3RS = CurrentDb.OpenRecordset("SELECT * FROM tblMP3_FileList", dbOpenDynaset)
        For Each F In Fldr.Files
        Debug.Print "Name"; Fldr.Name, "type; "; Fldr.Type, "Path: "; Fldr.Path
        Set objFolder = objShell.NameSpace(Fldr.Path & "\")       
        If Right(F.Name, 4) = ".mp3" Then
            CopyMP3RS.AddNew
                [COLOR="Red"][B]Debug.Print "File: "; F.Name, objFolder.GetDetailsOf(F.Name, 0)[/B][/COLOR]
                CopyMP3RS!FileLocation = F.Path
                CopyMP3RS!FileName = F.Name
                CopyMP3RS!FileSize = F.Size
            CopyMP3RS.Update
            End If
        Next F
    Rem ---------------------------------------
    For Each Fldr In Fldr.SubFolders
         Call Create_File_List(Fldr.Path)
    Next Fldr
End Sub

speakers_86 · Mar 6, 2012

I see you have F.Name there by itself. What does that return? Also, is the integer value in the GetDetailsOf function optional? Perhaps if you don't include the 0, maybe the name of the file is the default.

If you happen to use iTunes to manage your library, I put a sample db on this forum that scans your entire iTunes library.

Also, can you tell me what "Rem ----" is in your code? Also, what does the semi colon do in your debug statement? I've only ever used the ampersand (&). edit- And the comma, what does that do?

Steve R. · Mar 6, 2012

speakers_86 said:
I see you have F.Name there by itself. What does that return? Also, is the integer value in the GetDetailsOf function optional? Perhaps if you don't include the 0, maybe the name of the file is the default.

F.Name returns the actual name of the file. Microsoft used this format "objFolder.GetDetailsOf(strFileName, i)" in their example, so I substituted F.Name for strFileName. I also replaced the "i" with the numeral "0".

speakers_86 said:
If you happen to use iTunes to manage your library, I put a sample db on this forum that scans your entire iTunes library.

No I am not currently using iTunes, but thank-you for posting the database and sharing it. I am modifying a program that I posted here on the forum to copy music files to a USB drive, which I use in my car.

speakers_86 said:
Also, can you tell me what "Rem ----" is in your code? Also, what does the semi colon do in your debug statement? I've only ever used the ampersand (&). edit- And the comma, what does that do?

"REM" is another way to identify that all the following text is not code but is for commenting purposes. The semicolon is a formatting symbol that means that there is no space following the variable. The comma is used to format text to the next tab.

speakers_86 · Mar 6, 2012

Code:

Debug.Print "File: "; F.Name, objFolder.GetDetailsOf(F.Name, 0)

So

F.name="Your MP3.mp3"

Is that right?

What do you expect to get out of the getdetailsof function? It seems to me that you already have the info you want in F.name. Or is it simply an issue of including the full path to the file?

edit-Although it's not as fun as making your own app, there are other options out there. Here is something I used once, though I didn't hold on to it for some reason.

Steve R. · Mar 6, 2012

speakers_86 said:
F.name="Your MP3.mp3" ... Is that right? ... What do you expect to get out of the getdetailsof function?

Sort of. The file has associated (hidden) properties that I am attempting to retrieve. Take a look at this table to see what I am attempting to retrieve.

getdetails(Filename,i) is supposed to retrieve those hidden associated properties. It is sort of working. When fully copying the code provided by Microsoft, it does return the expected values. However, it requires extensive looping which would seem grossly inefficient if you are in a loop already. When I strip the MS sample code down to what I believe to be the minimum, it does NOT work as intended. It only reports the name of the extended property and NOT the value contained in the extended property.

For example, where i=16, the title of the property is "Artist", instead of getting the name of the artist, I only get the title of the property which is "Artist".

Steve R. · Sep 17, 2013

The genesis for this thread was that the car radio would not display all the proprieties associated with an MP3 song stored on a USB flash drive. So I was looking for a software solution.

Well, the car radio died, so I had a new one installed. The new radio does fully display the properties of the MP3 songs!!!!

So the issue was the technical hardware limitation of the prior radio.

speakers_86 · Sep 21, 2013

Good for you. I'm glad you got it sorted, but you never said anything about a radio before. How were you using Access in your car?

Steve R. · Sep 21, 2013

You mean that you weren't aware that car radios today can use MS Access!!

What I have is an Access program that creates a random playlist and stores that play list onto a USB flash drive. A copy of the MS Access program is attached should you wish to use it. Beats trying to do that manually.

The (old) car radio display failed to display all the properties associated with the filename of the song. (For example, the album name of a song was missing). Some were displayed correctly. So I figured it was a software issue associated with copying the songs.

The new car radio display shows the properties (for example: artist/album) associated with the song. So the problem wasn't with the copying operation, but with the radio's firmware. The new radio is much better, but it also represents several years of technological evolution.

isladogs · Jul 31, 2017

Hi Steve

Bit late to this party ... (just 4 years or so!)

I believe you're still active in this forum and thought you might be interested in this post Obtaining extended file properties using VBA.
I added it to the sample databases area a few weeks ago in response to a question from another user

I wish I had seen your thread earlier as it would have been useful at the time I was developing my own solution

I thought your MP3 SongCopy database sounded interesting so I had a look at it. It certainly looks good and created a random playlist as designed

A couple of issues:
1. It doesn't compile - easily fixed by adding

Code:

Global ctrl As Control

2. The displayed file name cuts off the first 2 letters of the title when viewed in Explorer:

I haven't yet checked it on my car radio display but assume it will show the truncated file name also.
Did you have any problems with this?

How to Retrieve Extended File Properties (1 Viewer)

Steve R.

Retired

speakers_86

Registered User.

Steve R.

Retired

speakers_86

Registered User.

Steve R.

Retired

Steve R.

Retired

speakers_86

Registered User.

Steve R.

Retired

Attachments

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread