Displaying Extended File Information


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.

image from book
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 image from book References command and create a reference to Microsoft Shell Controls and Automation.

CROSS-REFERENCE  

This procedure prompts the user for a directory by a function named GetDirectory . The GetDirectory function uses a Windows API function, which is described in Chapter 12.

CD-ROM  

This example, named image from book  file information.xlsm , is available on the companion CD-ROM.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net