# Getting file properties with objFolder.GetDetailsOf(objFolderItem,156)



## chaostheory (May 18, 2007)

So i am trying to achieve a mixture of stuff but i'm getting into code that doesn't seem to behave like regular VBA does. I am a software tester and one of the things we have to do is get file information. Currently this is done by hand, on ever file, right click, hit properties, hit the details tab and record that stuff into a spreadsheet, name, version number, etc. So i managed to find a couple different scripts that kind of do what i want, but i can't seem to put them together to get the whole of what i want.

The first script, pulls a list of all files in a folder (and all sub folders, and returns the path, file name, size and last date modified. This works great but the list of what it can return is only about 30 fields. Some of the properties i need cannot be gotten with this code. Here is the 1st code, this works fine, just posting to show what im doing..

```
Sub ListFiles()
    iRow = 5
    Call ListMyFiles(Range("C1"), Range("C2"))
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
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
    
    Call getMoreDetail(Range("C1")) 'I added this to get the rest of the details...
End Sub
```
That works great like i said, but is limited in what it can pull, so i kept researching.

I found another script that digs wayyyyyy deeper and has exactly what i need. It pulls every conceivable property of a specific file. Great now i can see which "property ID" i need to pull to get the version number and other things i need. Only problem is i can't put them together. Whenever i try to put a variable in for the path and the filename, it errors out.

Here is the unaltered code, which pulls every detail on one file, i used this to find the ID's that i wanted to pull for each file. Then i tried to modify it to cycle through all files and pull the ID's i wanted, that code is below this one, and does not work.

```
Sub test()

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("c:\Salient UXT\Dev")
Set objFolderItem = objFolder.ParseName("scdatutl.exe")

For I = 0 To 300 ' Don't remember the number of details ...
  Range("A1").Offset(I, 0) = I & "=" & objFolder.GetDetailsOf(objFolderItem, I)
Next

End Sub
```
And here is what i "wanted" to do...doesn't work but you can see my process flow by how it's written.

```
Sub getMoreDetail(mySourcePath)
Dim R As Double, C As Double

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(mySourcePath)

For C = 1 To 20 'Look for the File Name header
    If Range("A1").Offset(3, C) = "File Name" Then
        For R = 1 To 300 'Once found cycle 300 rows or until a blank cell is encountered
            If Range("A1").Offset(3 + R, C) = "" Then
                Exit For
            End If
            theFile = Range("A1").Offset(3 + R, C) 'Store the filename
            Set objFolderItem = objFolder.ParseName(theFile) 'Set the filename to objFolderItem
            Range("A1").Offset(3 + R, C + 3) = I & "=" & objFolder.GetDetailsOf(objFolderItem, 156) 'pull Detail ID 156 on the filename
            Range("A1").Offset(3 + R, C + 4) = I & "=" & objFolder.GetDetailsOf(objFolderItem, 176) 'pull detail ID 176 on the filename
        Next R
    End If
End Sub
```
Whenever i try to put a variable in place of the scdatutl.exe, it errors out. I need to be able to put that in a loop and have it cycle through all the filenames the first script returned. For instance in this case, mySourcePath = "c:\Temp". It shows that in the Sub line, but when i get to the Set objFolder = objShell.Namespace(mySourcePath), then it shows as "Nothing"

All help is appreciated this is over my head. The second script listed, which pulls all ID's, if i could figure out how to get that to accept a variable instead of only one file i could use that alone to pull all the details i need, but i just can't get it and my google searching is not turning up answers that i understand.

Thanks for the help!


----------



## chaostheory (May 18, 2007)

As it always goes, when i can't figure out i post here, but by the time i come back i have figured out how to do it already.

Here is my code. This will list all files in a folder (and all sub folders of that folder if you put TRUE), and give some details about the files, size, name, version, etc.

Add reference Microsoft Scripting Runtime

C1 is the path
C2 is the TRUE/FALSE to do subfolders
C3 is the Max number of items to pull

B6 is where the data starts.

```
Dim iRow

Sub ListFiles()

    Application.ScreenUpdating = False
    
    Call ClearPages
    
    iRow = 6 'Row to start putting data on
    Call ListMyFiles(Range("C1"), Range("C2"), Range("C3"))
    
    Application.ScreenUpdating = True
    
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders, totalRows As Integer)
Dim R As Double
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    
    For Each myFile In mySource.Files
        Cells(iRow, 2).Value = myFile.ParentFolder
        Cells(iRow, 3).Value = myFile.Name
        Cells(iRow, 5).Value = myFile.Size
        Cells(iRow, 9).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True, totalRows)
        Next
    End If
    
    For R = 1 To totalRows
        If Range("C5").Offset(R, 0) <> "" Then
        filePath = Range("B5").Offset(R, 0) & "\" & Range("C5").Offset(R, 0)
        Range("F5").Offset(R, 0) = GetFileProperties1(filePath) 'Product Version
        End If
    Next R
    For R = 1 To totalRows
        If Range("C5").Offset(R, 0) <> "" Then
        filePath = Range("B5").Offset(R, 0) & "\" & Range("C5").Offset(R, 0)
        Range("G5").Offset(R, 0) = GetFileProperties2(filePath) 'File Version
        End If
    Next R
    
    
End Sub

Function GetFileProperties1(strFilewFullPath)
Const arrSize = 300
Dim strFile As String, strFileName
Dim strPath As String, sTemp As Variant
Dim I As Integer
Dim objShell As Object
Dim arrHeaders(arrSize)
Dim objFolder As Object
Dim oFile As Object
Dim arTemp

Set objShell = CreateObject("Shell.Application") '=====

strFile = Dir(strFilewFullPath)
strPath = Left(strFilewFullPath, InStrRev(strFilewFullPath, "\") - 1)
arTemp = Split(strFilewFullPath, "\")
strFileName = arTemp(UBound(arTemp))

Set objFolder = objShell.Namespace(strPath & "\")

For I = 0 To arrSize
  arrHeaders(I) = objFolder.GetDetailsOf(objFolder.Items, I)
Next I

GetFileProperties1 = ""

Set oFile = objFolder.ParseName(strFileName)

sTemp = arrHeaders(156)
sTemp = objFolder.GetDetailsOf(oFile, 271)
If IsNull(sTemp) Then sTemp = "Missing"
GetFileProperties1 = GetFileProperties1 & sTemp

End Function
Function GetFileProperties2(strFilewFullPath)
Const arrSize = 300
Dim strFile As String, strFileName
Dim strPath As String, sTemp As Variant
Dim I As Integer
Dim objShell As Object
Dim arrHeaders(arrSize)
Dim objFolder As Object
Dim oFile As Object
Dim arTemp

Set objShell = CreateObject("Shell.Application") '=====

strFile = Dir(strFilewFullPath)
strPath = Left(strFilewFullPath, InStrRev(strFilewFullPath, "\") - 1)
arTemp = Split(strFilewFullPath, "\")
strFileName = arTemp(UBound(arTemp))

Set objFolder = objShell.Namespace(strPath & "\")

For I = 0 To arrSize
  arrHeaders(I) = objFolder.GetDetailsOf(objFolder.Items, I)
Next I

GetFileProperties2 = ""

Set oFile = objFolder.ParseName(strFileName)

sTemp = arrHeaders(156)
sTemp = objFolder.GetDetailsOf(oFile, 156)
If IsNull(sTemp) Then sTemp = "Missing"
GetFileProperties2 = GetFileProperties2 & sTemp

End Function

Sub ClearPages()

Sheets("Sheet1").Range("B6:C1000").ClearContents
Sheets("Sheet1").Range("E6:J1000").ClearContents

End Sub
```


----------

