More Robust Directory List Generator?

[binar]

Fellow Forum Members,
Attached is a XLA file that generates a list of all filenames and associated subdirectories. It includes details such as NAME, DATE and SIZE. In addition, the filenames are hyperlinked to the actual file. In short, it's a very handy little tool that was created by someone who gave it away for free and who no longer supports this tool.

Is it possible to modify this XLA file so that it goes beyond just including NAME, DATE, and SIZE details? I would like it to include any of the details I have selected (checked off) inside the Windows7 Explorer "Choose Details" dialog window.

For example, I'm interested in generating a file list that includes the following details:
NAME, DATE, SIZE, LENGTH, 35mm FOCAL LENGTH, WIDTH

If this XLA tool can't be modified, is there a similar Excel 2007 add on tool that will support inclusion of the more exotic file details such as LENGTH and WIDTH in a list that is hyperlinked? Any help will be greatly appreciated

[shrivallabha]

Hi binar,

If you want to modify the code then you'll need password to open the VBA project as this add-in's VBA project is password protected.

On the other hand you will find some good coding samples on internet which use "FileSystemObject". You can start with any of them and work out a solution.

Like this page:http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder

[binar]

Hi binar,

If you want to modify the code then you'll need password to open the VBA project as this add-in's VBA project is password protected.

On the other hand you will find some good coding samples on internet which use "FileSystemObject". You can start with any of them and work out a solution.

Like this page:http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder

Shrivallabha,
Thanks for your post. I would appreciate it a lot if you or any one else in this forum can help me in finding a solution for what I want to do.

I'm trying to create a list of video files and would like to add other file parameters available for choosing in the Windows7 Choose Detail dialog window. Below are three examples of some of the Parameters I was trying to plug into the VBA code shown below:

i.e.
myfile.Comments
myFile.width
myfile.Length (i.e. length of video)

Below is the code I'm trying to modify. I have tried adding columns with these examples, and the columns are just left blank.

[vba]
Dim iRow
Sub ListFiles()
iRow = 11
Call ListMyFiles(Range("C7"), Range("C8"))
End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastModified
iRow = iRow + 1
Cells(iRow, iCol).Value = myFile.Length
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Width
iCol = iCol + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub

[/vba]

In the code above I added the LENGTH and WIDTH parameters and neither does not work. I'm also trying to get the File Name parameter to show as a hyperlink, also without success.

Attached as a zip file is the Excel file that was available for free download at Excelexperts (unlike the XLA file I uploaded in my first post, this file does not seem to be password protected). I would be very grateful if anyone out there could let me know what is the correct coding needed so that I could add additional parameters (i.e. LENGTH, WIDTH) in addition to PATH, NAME, DATE, and SIZE parameters. Any help will be greatly appreciated. Thanks.

[snb]

Have a look over here:

http://www.snb-vba.eu/VBA_Bestanden_en.html

[binar]

Have a look over here:

http://www.snb-vba.eu/VBA_Bestanden_en.html

SNB,
Thanks for the post. I looked over the webpage and I am not too sure what I should be looking at. Can you provide more specific information that could help me figure this out. Thank you very much.

[snb]

You can find code to retrieve music files properties
http://www.snb-vba.eu/VBA_Bestanden_en.html#L64

and
photofiles properties

[shrivallabha]

Shrivallabha,
Thanks for your post. I would appreciate it a lot if you or any one else in this forum can help me in finding a solution for what I want to do.
:
:
. Below are three examples of some of the Parameters I was trying to plug into the VBA code shown below:
i.e.
myfile.Comments
myFile.width
myfile.Length (i.e. length of video)

Below is the code I'm trying to modify. I have tried adding columns with these examples, and the columns are just left blank.
:
:
:
:
In the code above I added the LENGTH and WIDTH parameters and neither does not work. I'm also trying to get the File Name parameter to show as a hyperlink, also without success.

Hi Binar,

The list is coming blank as the properties or attributes are not applicable to the object you are using. So to know the properties and methods available you can use employ early binding.

1. In Visual Basic Editor window set reference to "Microsoft Scripting Runtime".
2. And Dim these variables specifically as below. This will prompt you with the methods and properties available for the Object.
[VBA]Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Dim MyObject As FileSystemObject
Dim mySource As Folder
Dim myFile As File
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastModified
iRow = iRow + 1
Cells(iRow, iCol).Value = myFile.Length
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Width
iCol = iCol + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub
[/VBA]

snb has provided you with snippets which you need to use in your code.

@snb: Superb work

[Tinbendr]

[VBA]Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Dim MyObject As FileSystemObject
Dim mySource As Folder
Dim myFile As File
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastModified
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Type

iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub[/VBA]
These two counters were backwards, too.

[binar]

Thanks to all for there post. I have looked over everything and the reality is that the coding knowledge needed to make this work is something I don't have. I know I need to use one of the three DURATION related objects shown below:

[VBA]
With CreateObject("shell.application").namespace(***.Path & "\")
c370 = .getdetailsof(.Items.Item(***.Name), 21)
End With

With CreateObject("shell.application").namespace(***.Path & "\") c470 = split(.getdetailsof(.Items.Item(***.Name), -1),vblf)(3)
End With

With CreateObject("shell.application").namespace(***.Path & "\") c470a=mid(join(filter(split(.getdetailsof(.Items.Item(***.Name), -1),vblf),"Duration: "),""),11)
End With

[/VBA]

My problem is I don't know how to integrate it with Tinbendr's code below:

[VBA]
Sub ListMyFiles(mySourcePath, IncludeSubfolders) Dim MyObject As FileSystemObject Dim mySource As Folder Dim myFile As File Set MyObject = New Scripting.FileSystemObject Set mySource = MyObject.GetFolder(mySourcePath) On Error Resume Next For Each myFile In mySource.Files iCol = 2 Cells(iRow, iCol).Value = myFile.Path iCol = iCol + 1 Cells(iRow, iCol).Value = myFile.Name iCol = iCol + 1 Cells(iRow, iCol).Value = myFile.Size iCol = iCol + 1 Cells(iRow, iCol).Value = myFile.DateLastModified iCol = iCol + 1 Cells(iRow, iCol).Value = myFile.Type iRow = iRow + 1 Next If IncludeSubfolders Then For Each mySubFolder In mySource.SubFolders Call ListMyFiles(mySubFolder.Path, True) Next End If End Sub
[/VBA]

Any assistance in code integration will be tremendously appreciated. Thank you very much.

[Kenneth Hobs]

Binar, when you post code and it does not format properly, try pasting to an Excel range and then copy and paste that between VBA code tags.

I don't see a 35mm Focal Length in the snb's fine list. To test one that you know exists, replace the *** with your folder or folder+filename.ext.

Simply add one more iCol line to add the extra item(s). You will probably need an error catching routine as some of those properties may not exist for each file type.

If you want to use early binding and don't mind installing dsofile.dll, try Chip Pearson's routines. Of course these are advanced but are worth the effort sometimes. http://www.cpearson.com/excel/DocProp.aspx

If you ever get into vb.net and want another example using dsofile.dll, see my lesson here. http://www.wpuniverse.com/vb/showthr...-106-KeyMacros

[snb]

[VBA]sub snb()
for each fl in createobject("scripting.filesystemobject").getfolder(Range("C7").value).fil es
With CreateObject("shell.application").namespace(Range("C7").value & "\")
msgbox = .getdetailsof(.Items.Item(dir(fl)), 21)
End With
next
End sub[/VBA]

[binar]

Binar, when you post code and it does not format properly, try pasting to an Excel range and then copy and paste that between VBA code tags.

I don't see a 35mm Focal Length in the snb's fine list. To test one that you know exists, replace the *** with your folder or folder+filename.ext.

Simply add one more iCol line to add the extra item(s). You will probably need an error catching routine as some of those properties may not exist for each file type.

If you want to use early binding and don't mind installing dsofile.dll, try Chip Pearson's routines. Of course these are advanced but are worth the effort sometimes. http://www.cpearson.com/excel/DocProp.aspx

If you ever get into vb.net and want another example using dsofile.dll, see my lesson here. http://www.wpuniverse.com/vb/showthr...-106-KeyMacros

Kenneth,
Thanks for your post. Let me give you an update.
First, I tested SNB's script below and got the following error:

COMPILE ERROR - Function call on left hand side of assignment must return variant or object

[vba]

Sub snb()
For Each fl In createobject("scripting.filesystemobject").getfolder(Range("C7").value).fil es
With CreateObject("shell.application").namespace(Range("C7").value & "\")
msgbox = .getdetailsof(.Items.Item(dir(fl)), 21)
End With
Next
End Sub
[/vba]


It hangs up on the msgbox row.

Second, I put some effort at coding by following your instructions in your last post. Below for display for everyone to see is the way I understand it. I'm replacing all " *** " with " Range("C7").value & "\" " since this is the location on the spreadsheet where the path is inputted. Nevertheless, I tested it out and got an error.

[vba]
Dim iRow
Sub ListFiles()
iRow = 11
Call ListMyFiles(Range("C7"), Range("C8"))
With CreateObject("shell.application").namespace(Range("C7").value & "\")
c470a=mid(join(filter(split(.getdetailsof(.Items.Item(Range("C7").value & "\"), -1),vblf),"Duration: "),""),11)
End With

End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastModified
iRow = iRow + 1
Cells(iRow, iCol).Value = myFile.Length
iCol = iCol + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub

[/vba]

I would be tremendously appreciative if you can at your convenience or anyone else following this thread post some VBA code that accomplishes the following:

1. Generates a list that includes the follwing File Paremeters: NAME, SIZE, DATE, LENGTH (a.k.a. DURATION, however it displays as LENGTH in Windows7 Windows Explorer)

2. All the data in the NAME Parameter column is coded so that it appears HYPERLINKED so that when I click on the hyperlink it launches my Windows Media Player.

3. The icing in the cake would be if an easy way to add more File Parameters within the worksheet using drop down menus could be coded. So if in the future I would like to add the COMMENT Parameter, it could be done using a drop down menu from within the worksheet. Obviously, if this idea is too complex to code please disregard it.

Lastly, I took a look at using dsofile.dll and it seems to be an advanced level coding option that is not going to be a straight forward approach for a coding novice.

Again. any help that will let me solve this problem will be tremendously appreciated. Thanks.

[Kenneth Hobs]

Obviously, you need to remove the "=" from your MsgBox line.

You can do all of that but do each part and then check.

When I get time, I will help you with more specific examples. Most of the work has already been done for you.

I know that an AVI file type has a duration. To test the duration, I am lazy and just poked the duration value into column A. Save a workbook and add:
[vba]Sub snb()
Dim fl As Variant
Dim i As Long
i = 2

Range("C7").Value2 = ThisWorkbook.Path
For Each fl In CreateObject("scripting.filesystemobject").getfolder(Range("C7").Value).Fil es
With CreateObject("shell.application").Namespace(Range("C7").Value & "\")
Range("A" & i).Value2 = .getdetailsof(.Items.Item(Dir(fl)), 21) 'Duration
End With
i = i + 1
Next
End Sub
[/vba]

[Kenneth Hobs]

I could not test this much on Vista since the Duration property values are not being shown for my avi and flv files.

Set the two references detailed in my comments.

[VBA]Sub FileDetails()
Range("C7").Value2 = ThisWorkbook.Path

ListMyFiles Range("C7").Value2, 11
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

' Tools > References > Microsoft Scripting Runtime
' Tools > References > Microsoft Shell Controls and Automation
Sub ListMyFiles(mySourcePath As String, iRow As Long, _
Optional IncludeSubfolders As Boolean = True)

Dim myObject As Scripting.FileSystemObject
Dim mySource As Scripting.Folder
Dim myFile As Scripting.File
Dim mySubFolder As Scripting.Folder
Dim iCol As Integer
Dim wShell As Shell

Set wShell = New Shell
Set myObject = New Scripting.FileSystemObject
Set mySource = myObject.GetFolder(mySourcePath)

On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value2 = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value2 = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value2 = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value2 = myFile.DateLastModified
iCol = iCol + 1
Cells(iRow, iCol).Value2 = wShell.Namespace(myFile.Path).GetDetailsOf(myFile.Name, 21)
iCol = iCol + 1
'Range("A" & iRow).Hyperlinks.Add Range("A" & iRow), myFile.Path & "\" & myFile.Name, , , myFile.Name
Range("A" & iRow).Hyperlinks.Add Range("A" & iRow), myFile.Path & "\" & myFile.Name, , , myObject.GetBaseName(myFile.Name)
iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
ListMyFiles mySubFolder.Path, iRow, True
Next
End If
End Sub[/VBA]

[binar]

Obviously, you need to remove the "=" from your MsgBox line.

You can do all of that but do each part and then check.

When I get time, I will help you with more specific examples. Most of the work has already been done for you.

I know that an AVI file type has a duration. To test the duration, I am lazy and just poked the duration value into column A. Save a workbook and add:
[vba]Sub snb()
Dim fl As Variant
Dim i As Long
i = 2

Range("C7").Value2 = ThisWorkbook.Path
For Each fl In CreateObject("scripting.filesystemobject").getfolder(Range("C7").Value).Fil es
With CreateObject("shell.application").Namespace(Range("C7").Value & "\")
Range("A" & i).Value2 = .getdetailsof(.Items.Item(Dir(fl)), 21) 'Duration
End With
i = i + 1
Next
End Sub
[/vba]

Kenneth,
Thanks for your post.

Just an update. I tested your code with my Excel 2007. I entered the path in Cell C7 and hit the LIST button and it didn't work. The only thing that happened was the path in Cell C7 changed to something else. Still trying to figure out what is happening. I'm going to try it again tomorrow where I work.

Moreover, I have posted 3 PNG files that show Detail Parameters I'm interested in and also show how they appear within my Windows7 Windows Explorer. One PNG is for Music, and the other PNG is for Video. The third PNG file shows the "Choose Details" dialog window. What is worth noting is how it offers both DURATION and LENGTH. I'm not sure what the difference is between the two but I picked both and what seems to work correctly is the LENGTH Detail Parameter. I'm pointing this out because I have noticed SNB's webpage shows code for DURATION but none for LENGTH. So I'm now wondering whether LENGTH is supported with any code.

As you can see from my screen captures there are a multitude of File Detail Parameters to pick from inside the "CHOOSE DETAILS" dialog window. I'm only interested in a few. Nevertheless, do you think Excel VBA coding is able to support any of the Detail Parameters listed within the "CHOOSE DETAILS" dialog window as long as they are checked off? Just curious to know. Any help you can provide will be greatly appreciated. Thanks.

[Kenneth Hobs]

Please only quote parts of posts if you need to point out some part. Otherwise just say something like, "in Kenneth's post #13, the file with the Duration values are PNG files typically though c:\windows\clock.avi has a duration value for those with Windows XP-".

There are 42 property details that you can get. I could post a routine that can put all into an array but if you only need a few, it should be easy to see how that is done by my code. Notice that you can put the name or word of the property detail and/or the value as well.

Obviously, when testing code, try to test in a blank sheet or even better yet, a blank workbook. Notice that I deleted the contents of the activesheet and put the saved workbook's path into cell C7.

Don't forget to set the two references.

[vba]Sub FileDetails()
ActiveSheet.UsedRange.Clear
Range("C7").Value2 = ThisWorkbook.Path

ListMyFiles Range("C7").Value2, 11
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

' Tools > References > Microsoft Scripting Runtime
' Tools > References > Microsoft Shell Controls and Automation
Sub ListMyFiles(mySourcePath As String, iRow As Long, _
Optional IncludeSubfolders As Boolean = True)

Dim myObject As Scripting.FileSystemObject
Dim mySource As Scripting.Folder
Dim myFile As Scripting.File
Dim mySubFolder As Scripting.Folder
Dim iCol As Integer
Dim wShell As Shell

Set wShell = New Shell
Set myObject = New Scripting.FileSystemObject
Set mySource = myObject.GetFolder(mySourcePath)

On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value2 = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value2 = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value2 = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value2 = myFile.DateLastModified
Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
iCol = iCol + 1

With wShell.Namespace(mySource.Path)
Cells(iRow, iCol).Value2 = .GetDetailsOf(myFile.Name, 21) 'Duration word.
iCol = iCol + 1
Cells(iRow, iCol).Value2 = .GetDetailsOf(.ParseName(myFile.Name), 21) 'Duration value.
End With

'Range("A" & iRow).Hyperlinks.Add Range("A" & iRow), myFile.Path , , , myFile.Name
Range("A" & iRow).Hyperlinks.Add Range("A" & iRow), myFile.Path , , , myObject.GetBaseName(myFile.Name)

iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
ListMyFiles mySubFolder.Path, iRow, True
Next
End If
End Sub

[/vba]

[binar]

Kenneth,
A million thanks for the code you are sharing in your #16 post. I tested out your code and I made sure to start with a clean worksheet as you instructed. In cell C7 I entered the PATH for the top level folder containing my music video collection. Then I hit the RUN button in the Macro Dialog window and I got the following error:

"Compile Error: User Defined type not defined"
(I have attached the XLSM file I am trying to make work)

The code stops working in area "C" indicated in the screen capture below. I tried tweaking the code to see if I could get it to work but I have to admit I still don't understand all of the levers and gears relating to your code. I have a general idea, but not enough to fix it on my own. Below I have posted a screen capture of your code highlighted with Letter Identifiers.

In addition, what I'm still trying to understand is what I need to do to your code so that I can make it fetch any of the other 42 property details you mentioned.

I am assuming the following:
"G" = fetches PATH data and places it in Column A
"H" = fetches NAME data and places it in Column B
"I" = fetches SIZE data and places it in Column C
"J" = fetches DATE LAST MODIFIED data and places it in Column D
"K" = fetches DATE as a number format and places it in Column E
"L" = Has me confused because my Windows Explorer shows it as LENGTH

Could I now correctly assume the following?
If I want to add FRAME WIDTH & FRAME HEIGHT I will need to add these new lines of code -

[vba]
Cells(iRow, iCol).NumberFormat = "frame width"
iCol = iCol + 1

Cells(iRow, iCol).NumberFormat = "frame height"
iCol = iCol + 1
[/vba]

If you could clarify this for me I would appreciate it. Again, a million thanks for your generosity in developing a very cool File List Generator. I think it's going to be light years ahead of the List Generator I attached in my #1 Post.

[Jan Karel Pieterse]

I think the answer is in post#14:

' Tools > References > Microsoft Scripting Runtime
' Tools > References > Microsoft Shell Controls and Automation

[Paul_Hossler]

There's a list of enumerates (sounds good )

Apparently, based on the post below, there is some OS changes

http://www.kixtart.org/forums/ubbthr...=160880&page=1


HTH

Paul

[shrivallabha]

I think the answer is in post#14:

' Tools > References > Microsoft Scripting Runtime
' Tools > References > Microsoft Shell Controls and Automation

Its there in #16 as well [his comments are in the code and last line reminds to set them].