Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The example in this section displays extended file properties for all files in a specified directory. The information that's available depends on the file type. For example, image files have properties such as Camera Model and Dimensions; audio files have properties such as Artist, Title, Duration, and so on. Following is a complete list of extended properties retrieved by this procedure:

• Name

• Size

• Type

• Date Modified

• Date Created

• Date Accessed

• Attributes

• Status

• Owner

• Author

• Title

• Subject

• Category

• Pages

• Comments

• Copyright

• Artist

• Album Title

• Year

• Track Number

• Genre

• Duration

• Bit Rate

• Protected

• Camera Model

• Date Picture Taken

• Dimensions

• Program Description

• Audio sample size

• Audio sample rate

• Channels

The FileInfo procedure, which uses the Windows Shell.Application object, follows :

Sub FileInfo() Dim c As Long, r As Long, i As Long Dim FileName As Object 'FolderItem2 Dim objShell As Object 'IShellDispatch4 Dim objFolder As Object 'Folder3 ' Create the object Set objShell = CreateObject("Shell.Application") ' Prompt for the folder Set objFolder = objShell.Namespace(GetDirectory) ' Insert headers on active sheet Worksheets.Add c = 0 For i = 0 To 34 If i = 27 Or i = 28 Or i = 29 Or i = 31 Then 'Nothing. These items are not used Else c = c + 1 Cells(1, c) = objFolder.GetDetailsOf(objFolder.Items, i) End If Next i ' Loop through the files r = 1 For Each FileName In objFolder.Items c = 0 r = r + 1 For i = 0 To 34 If i = 27 Or i = 28 Or i = 29 Or i = 31 Then 'Nothing. These items are not used Else c = c + 1 Cells(r, c) = objFolder.GetDetailsOf(FileName, i) End If Next i Next FileName ' Make it a table ActiveSheet.ListObjects.Add xlSrcRange, _ Range("A1").CurrentRegion End Sub

Figure 27-4 shows part of the output of this procedure.

Figure 27-4: A table of information about the files in a directory.

This example uses late binding to create a Shell.Application object, so the objects are declared generically. To use early binding, use the VBE Tools References command and create a reference to Microsoft Shell Controls and Automation.