Using Shell Namespace method "GetDetailsOf" causes excel to crash

Senior, Russell (R.A.) 0 Reputation points
2023-01-25T12:42:35.5566667+00:00

I'm using a vba script to summarize a list of data files in certain folders so I can manage those files (mark important, not important, whether I've used them in presentations, etc.). The files are created by a program called Vector CANape and are MDF file type (don't think that's important). The program uses the "comment" property of the file to store comments I place while recording the data. I'm using a Shell application to access the extra properties and the "getdetailsof" method.

Unfortuantely, the line I use to pull in those comments no longer works on my new laptop. Excel crashes with no error message.

RunLog_Table.ListColumns("File Comments").DataBodyRange(foundrow, 1).Value = objFolder.GetDetailsOf(objFolderItem, 24)

I'm not sure if I'm missing an add-in (don't have my old laptop anymore where this script worked). I've tried "on error resume next", to no avial. I'm open to ideas to either fix this or another way to get the file comments. Full script below.

 Dim FSO, LogPath, file, foundrow, oShell, i
    Dim objFolder, objFolderItem
    Dim detail
    Dim SearchResult As Range
    Dim PathList_Table, RunLog_Table As ListObject
    Dim sProgram, FileType
    
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oShell = CreateObject("Shell.Application")
    Set PathList_Table = Worksheets("Setup").ListObjects("PathList")
    Set RunLog_Table = Worksheets("Run Log").ListObjects("RunLog")

    
    For i = 1 To PathList_Table.Range.Rows.Count - 1
        
        LogPath = PathList_Table.ListColumns("Path").DataBodyRange(i, 1).Value
        sProgram = PathList_Table.ListColumns("Program").DataBodyRange(i, 1).Value
        FileType = PathList_Table.ListColumns("Type").DataBodyRange(i, 1).Value
        
        For Each file In FSO.GetFolder(LogPath).Files
            If UCase(FSO.getextensionname(file.Path)) = UCase(FileType) Then
                Set SearchResult = RunLog_Table.ListColumns("FileName").Range.Find(file.Name)
                If SearchResult Is Nothing Then
                    RunLog_Table.ListRows.Add
                    RunLog_Table.ListColumns("Filename").DataBodyRange(RunLog_Table.DataBodyRange.Rows.Count, 1).Value = file.Name
                Else
                    foundrow = SearchResult.Row - RunLog_Table.HeaderRowRange.Row
                    RunLog_Table.ListColumns("Date Created").DataBodyRange(foundrow, 1).Value = file.datecreated
                    RunLog_Table.ListColumns("Program").DataBodyRange(foundrow, 1).Value = sProgram
                    RunLog_Table.ListColumns("FilePath").DataBodyRange(foundrow, 1).Value = file.Path
                    'MsgBox (file.parentfolder)
                    If RunLog_Table.ListColumns("File Comments").DataBodyRange(foundrow, 1).Value = "" Then
                        Set objFolder = oShell.Namespace(file.parentfolder & "\")
                        Set objFolderItem = objFolder.ParseName(file.Name)
                        On Error Resume Next
                        RunLog_Table.ListColumns("File Comments").DataBodyRange(foundrow, 1).Value = objFolder.GetDetailsOf(objFolderItem, 24)
                        On Error GoTo 0
                        
                    End If
                End If
            End If
        Next
    Next
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,687 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,715 questions
{count} votes