Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 03-05-2012, 06:18 PM   #1
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,655
Thanks: 89
Thanked 98 Times in 86 Posts
Steve R. will become famous soon enough Steve R. will become famous soon enough
How to Retrieve Extended File Properties

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
                Debug.Print "File: "; F.Name, objFolder.GetDetailsOf(F.Name, 0)
                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

__________________
Now using Linux (Ubuntu).
Doing database development with Firefox/MariaDB.
Steve R. is offline   Reply With Quote
Old 03-05-2012, 06:53 PM   #2
speakers_86
I am jack's comment.
 
speakers_86's Avatar
 
Join Date: May 2007
Location: JBLM, Wa
Posts: 1,919
Thanks: 11
Thanked 158 Times in 119 Posts
speakers_86 will become famous soon enough
Re: How to Retrieve Extended File Properties

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?
__________________
If you look, you can find anything.
Google is your friend.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access 2010 screw this! I went back to 2007
Windows 7
speakers_86 is offline   Reply With Quote
Old 03-05-2012, 08:13 PM   #3
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,655
Thanks: 89
Thanked 98 Times in 86 Posts
Steve R. will become famous soon enough Steve R. will become famous soon enough
Re: How to Retrieve Extended File Properties

Quote:
Originally Posted by speakers_86 View Post
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".

Quote:
Originally Posted by speakers_86 View Post
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.

Quote:
Originally Posted by speakers_86 View Post
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.

__________________
Now using Linux (Ubuntu).
Doing database development with Firefox/MariaDB.
Steve R. is offline   Reply With Quote
Old 03-05-2012, 08:26 PM   #4
speakers_86
I am jack's comment.
 
speakers_86's Avatar
 
Join Date: May 2007
Location: JBLM, Wa
Posts: 1,919
Thanks: 11
Thanked 158 Times in 119 Posts
speakers_86 will become famous soon enough
Re: How to Retrieve Extended File Properties

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.
__________________
If you look, you can find anything.
Google is your friend.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access 2010 screw this! I went back to 2007
Windows 7

Last edited by speakers_86; 03-05-2012 at 08:33 PM.
speakers_86 is offline   Reply With Quote
Old 03-06-2012, 10:43 AM   #5
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,655
Thanks: 89
Thanked 98 Times in 86 Posts
Steve R. will become famous soon enough Steve R. will become famous soon enough
Re: How to Retrieve Extended File Properties

Quote:
Originally Posted by speakers_86 View Post
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".
__________________
Now using Linux (Ubuntu).
Doing database development with Firefox/MariaDB.
Steve R. is offline   Reply With Quote
Old 09-17-2013, 04:47 AM   #6
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,655
Thanks: 89
Thanked 98 Times in 86 Posts
Steve R. will become famous soon enough Steve R. will become famous soon enough
Re: How to Retrieve Extended File Properties

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.
__________________
Now using Linux (Ubuntu).
Doing database development with Firefox/MariaDB.
Steve R. is offline   Reply With Quote
Old 09-20-2013, 03:36 PM   #7
speakers_86
I am jack's comment.
 
speakers_86's Avatar
 
Join Date: May 2007
Location: JBLM, Wa
Posts: 1,919
Thanks: 11
Thanked 158 Times in 119 Posts
speakers_86 will become famous soon enough
Re: How to Retrieve Extended File Properties

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?

__________________
If you look, you can find anything.
Google is your friend.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access 2010 screw this! I went back to 2007
Windows 7
speakers_86 is offline   Reply With Quote
The Following User Says Thank You to speakers_86 For This Useful Post:
Uncle Gizmo (03-19-2017)
Old 09-21-2013, 05:43 AM   #8
Steve R.
Retired
 
Steve R.'s Avatar
 
Join Date: Jul 2006
Location: Morehead City, North Carolina
Posts: 1,655
Thanks: 89
Thanked 98 Times in 86 Posts
Steve R. will become famous soon enough Steve R. will become famous soon enough
Re: How to Retrieve Extended File Properties

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.
Attached Files
File Type: accdb MP3_SongCopy.accdb (512.0 KB, 208 views)
__________________
Now using Linux (Ubuntu).
Doing database development with Firefox/MariaDB.

Last edited by Steve R.; 09-21-2013 at 05:54 AM.
Steve R. is offline   Reply With Quote
The Following User Says Thank You to Steve R. For This Useful Post:
Uncle Gizmo (03-19-2017)
Old 07-31-2017, 01:26 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,309
Thanks: 99
Thanked 2,129 Times in 1,978 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: How to Retrieve Extended File Properties

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?
Attached Images
File Type: png Capture.PNG (35.0 KB, 1133 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 07-31-2017 at 06:51 AM.
isladogs is offline   Reply With Quote
Reply

Tags
extended file properties

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to retrieve ImageList and imagecombo properties to TextBox With VBA besihad Modules & VBA 1 07-31-2009 09:38 AM
Retrieve Path and Server name of a file woknick Modules & VBA 3 05-11-2007 12:12 PM
File Extended Properties ezfriend General 3 03-30-2007 10:07 AM
How to get properties of .msg file saved on file system prgmdb Modules & VBA 3 06-20-2006 03:27 AM
Retrieve the file name of OLE Object GeekyGirl Forms 0 03-28-2006 08:30 PM




All times are GMT -8. The time now is 06:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World