Explains how to read some basic file properties such as the name and the size using the microsoft scripting runtime object library but here kartik's working with movie.
Files and he's interested in some of the more specific attributes such as the movie genre and release date so in this video we're going to look at how to use a windows shell object to get those.
Extra attributes i'm not actually going to work with movies myself in this video i'm going to use some photos stored in a separate folder mainly to give me the opportunity.
To show you some photos of some very good boys indeed feel free to work with you with whichever files you happen to have to hand i'm not going to provide you with.
The files for this video just use whatever you've got i've got started by adding a brand new blank excel workbook saved as a macro enabled file and in the visual basic.
To the microsoft shell controls and automation library so from the tools menu i'm going to choose references and then scroll through the list to find microsoft shell controls and automation.
I can then check that box and click ok then we can create a new subroutine let's call it something like get file properties or attributes if you prefer.
And i'll attempt to spell that even vaguely correctly and then we're ready to go let's start by writing the basic code that we need to process the collection.
Of files in that photos folder so i'll start by declaring a variable which is going to hold a reference to an instance of the shell class so i'll say dim sh as.
Shell 32 dot shell then i can declare a variable which is going to hold a reference to my folder object so i'll call this one fol as a.
Shell 32 dot folder
And then a variable called fil which will represent each individual file in that folder as a shell 32 dot folder item.I can then create a new instance of the shell class by saying set sh equals new shell 32 dot shell and then i can use a method of that shell object to get a reference to the.
Folder that i'm interested in so i can say set fol equals sh dot and then use the namespace method and then in some parentheses and some double quotes writing the path to the folder that i.
Want to get so c colon backslash photos close a double quotes and close around brackets finally we can write a simple for each loop to process the folder items so i.
Can say for each fil in fol dot items and then say next fil and then inside that loop let's just do.
Something simple like let's say f debug.print l dot name for example so we'll get the very basic name property.
If we then execute that subroutine or run it by pressing f5 or clicking the green triangle button it will give us a list of the files it's found in that folder printed to the media window.
Next i'd like to get some extra information about the file and to do that we're going to use the get details of method of a folder object i'll drop a link in the video.
Description to the page on the microsoft documentation site of the folder.getdetailsof method but the basic idea is that we pass in a reference to a file item and then a number which.
Indicates the piece of information we
Want to return there are some suggestions here in the parameters section of using numbers between 0 and 4 to get some basic file information.So just to show the absolute basics of this method in action let's head back to the visual basic editor and in my debug.print statement rather than saying file.name or fil.name we're going to say.
F o l dot get details of and then in some round brackets we can refer to the file that we're currently.
Looking at and then we're going to start with item number zero and then just to see more than just the file name again let's add a comma with a.
Copy pasted version of that same method and then we can look for item number one or value number one and then value number two as well and that should be sufficient just to demonstrate the.
Principle so if i clear the contents of the immediate window and then run that subroutine again we'll see we get three pieces of information this time using.
The get details of method now the documentation for the getdetailsof method is a little misleading because it indicates that you can enter values up to a number of four.
For the second parameter to get these basic bits of information but from a quick bit of basic testing before recording the video i found i could enter numbers up to 320 and still.
Return relevant values or properties of a file object just to give you an idea of what some of these extra attributes or properties might be try heading into a windows.
Explorer window so i'll go back to my photos of dogs and then i'll head to a details view so i can see some of these column headings listed across the top if i then right click on one of those.
Column headings and choose more you'll see the dialog box that appears provides you with a huge list of different properties you could display for different files.
Now of course not every single property is going to be relevant for every single type of file so things like video compression of course will only be relevant to video files things like 35.
Millimeter focal length will only be useful for for photos taken on a certain type of camera so if we just wanted to list out every.
Single property for the file what we could do is loop through the numbers from 0 to 320 and write each individual property out so to do that let's head back to the.
Visual basic editor ultimately we're going to want to write these values out onto a worksheet i think rather than into the media window so what i'm going to do here is take the.
Opportunity to declare a couple of variables for working with rows and columns numerically so i'm going to declare a variable called r as long and c as long.
On the same line there so we'll use those for the row number and the column number so just to wrap around my debug.print.
Statement what i'm going to do is say 4 c equals 0 to 320 that's the maximum number that actually printed anything useful in my testing earlier feel free to bump that.
Number up the number may have increased since the video was recorded we'll look at how we can get the information about what the name of each property is shortly.
Then i'm going to add a blank line and then just below my debug.print statement say next c and then rather than debug.printing this.
Specific number we're going to debug.print the value of the c variable i'm then going to get rid of everything to the end of that line and then we can.
Clear the contents of the immediate window and then run the subroutine again of course it'll take a little bit longer and you can see there are lots of empty.
Rows being printed here of course because not every property is relevant for a photo file but you can see lots of extra bits of information have been written out.
Lots of photo information there in the immediate window now let's write these values out into a worksheet i'm just going to use the existing sheet1 object for this and to.
Make sure i've cleared the contents of that worksheet each time i begin the subroutine i'm just going to start after my list of variable declarations by saying sheet1 dot cells dot clear.
Then inside the loop each time i encounter a new file i'd like to increment the value of my r variable to indicate that i want to move on to a new row so i'm going to say r equals r plus.
1 and then rather than debug.printing something we're going to assign a value to a cell so i'm going to say i'm going to remove debug.print and say.
Sheet1.cells open some parentheses i'll use my r variable for the row number and my c variable plus one for the column number plus one because c is beginning at zero.
And there isn't a zero numbered column in the worksheet of course then i want to assign that the value property or change the value with a value property by assigning the.