Consulting

Results 1 to 20 of 20

Thread: File properties using FSO and Dir

  1. #1

    File properties using FSO and Dir

    Does anyone have an example which uses both FSO and Dir to list files and their properties (such as Title and Subject)?
    Is it acceptable to do so?

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I have written this workbook which allows the user to search for any files which include the entered string and it also allows the user to select which file attributes you want displayed. I use this worikbook instead of the Microsoft, indexing and search facility which I find totally unsatisfactory because it slows the computer down all the time and isn't a proper search facility. ( I disable it on my computers)
    What is also much better is you end up with a excel worksheet with all the details which then allows me to, move or delte files using vBA
    to use it:
    There are two worksheet, when you select the control sheet,. a userfomr pops up which ask you for a string ( leave it blank if you want) it will then ask you to browse for a directory which is the starting point for the search. It searches all the directories under that .
    I hope this helps
    Attached Files Attached Files

  3. #3
    Hi
    Thanks for sending this.
    Do you know how to look for "Title" and "Subject"?

  4. #4
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Title and Subject are "properties" of a Microsoft Office file. They are not "general" file properties, e.g a .jpg file will have different properties or metadata.
    FSO will only find the file attributes which are general to all files types which I think are all the ones which my file accesses. I suspect that you are going to have to open the document to get access to those properties, however somebody else might know better!!

  5. #5
    OK thanks for your input.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think you can get the properties using FSO without opening the document

    However, the property field number changes from O/S version so you have to search for the field number (faGetFieldNumber) first, and then get the property (faGetFileProperty)

    My cheat sheet is also in the attachment


    Option Explicit
    
    Sub drv()
        MsgBox faGetFileProperty("C:\Users\Daddy\Documents\PropertiesTest.xlsm", "subject")
        MsgBox faGetFileProperty("C:\Users\Daddy\Documents\PropertiesTest.xlsm", "Title")
    End Sub
     
    
    Function faGetFileProperty(sFilename As String, sProperty As String) As Variant
        Dim iProp As Long
        Dim oFolder As Object, oFolderItem As Object
        Dim iFieldNumber As Long
        Dim sFolder As String, sFile As String
        
        faGetFileProperty = vbNullString
        On Error GoTo NiceExit
        iProp = faGetFieldNumber(sProperty)
        
        If iProp = -1 Then Exit Function
        With CreateObject("scripting.FileSystemObject")
            sFolder = .GetParentFolderName(sFilename)
            sFile = .GetFileName(sFilename)
        End With
        
        Set oFolder = CreateObject("shell.application").Namespace(sFolder & "\")
        Set oFolderItem = oFolder.parsename(sFile)
        
        faGetFileProperty = oFolder.GetDetailsOf(oFolderItem, iProp)
    NiceExit:
    End Function
    
    
    Private Function faGetFieldNumber(s) As Long
        Dim oFolder As Object
        Dim n As Long
        Dim sDesktop As Variant
        
        
        
        sDesktop = CreateObject("wscript.shell").specialfolders(10) & Application.PathSeparator
        Set oFolder = CreateObject("shell.application").Namespace(sDesktop)
        
        On Error GoTo Oops
        
             For n = 0 To 999
            If LCase(s) = LCase(oFolder.GetDetailsOf(oFolder.Items, n)) Then
                faGetFieldNumber = n
                Exit Function
            End If
        Next n
    Oops:
        Set oFolder = Nothing
        faGetFieldNumber = -1
         
    End Function
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635

  8. #8
    @Paul_Hossler, thanks for the sheet, I was aware of this issue though.
    @snb, I have seen this link before.

    No-one has yet to advise if Dir is able to extract the Title. Should I assume FSO is the only method I can use?

    For info, my issue is trying to read the Title for extensions such as DOCX and XLSX. For some reason it won't work for me. I queried in a post from 2016 (http://www.vbaexpress.com/forum/show...Of-not-working). It works for PDF but not for DOCX and XLSX.

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I don't understand what you want to communicate here:

    For info, my issue is trying to read the Title for extensions such as DOCX and XLSX. For some reason it won't work for me. I queried in a post from 2016 (http://www.vbaexpress.com/forum/show...Of-not-working). It works for PDF but not for DOCX and XLSX.

  10. #10
    With the code in the link in post #8, which uses FSO, I was unable to read the Title property for files with extension DOCX and XLSX.
    That's why in my OP I wanted to clarify if Dir and FSO can be used together to achieve this. Why together? I don't really know. I'm just trying to avoid Dir by itself because I don't fully understand it and just to keep as much of the code that I have.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Indigenous View Post
    @Paul_Hossler, thanks for the sheet, I was aware of this issue though.
    @snb, I have seen this link before.

    No-one has yet to advise if Dir is able to extract the Title. Should I assume FSO is the only method I can use?

    For info, my issue is trying to read the Title for extensions such as DOCX and XLSX. For some reason it won't work for me. I queried in a post from 2016 (http://www.vbaexpress.com/forum/show...Of-not-working). It works for PDF but not for DOCX and XLSX.

    My FSO macro does retrieve Title and Subject of DOCX files:

        MsgBox faGetFileProperty("C:\Users\Daddy\Documents\Lorem ipsum dolor sit amet.docx", "subject") & vbCrLf & vbCrLf & _
            faGetFileProperty("C:\Users\Daddy\Documents\Lorem ipsum dolor sit amet.docx", "Title")
    Capture.JPG


    1. I'm assuming that you actually have a Subject and Title on the DOCX or XLSX documents? Word and Excel do not add them automatically, so you must have entered them yourself

    2. Why would you expect the Dir() function to be able to extract the Title or anything else?

    Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

    SyntaxDir [ (pathname [ ,attributes ] ) ] The Dir function syntax has these parts:
    FSO would be the way I'd go if I wanted that information
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    @Paul_Hossler
    Thanks for clarifying that Dir is not suitable.
    I will persist with FSO.
    One thing which I thought may work was to install the reference "DSO OLE Document Properties Reader 2.1" as recommended by Chip Pearson (http://www.cpearson.com/excel/docprop.aspx). However this only worked with DOC and XLS for me, not for DOCX and XLSX.

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Indigenous View Post
    @Paul_Hossler
    I will persist with FSO.
    One thing which I thought may work was to install the reference "DSO OLE Document Properties Reader 2.1" as recommended by Chip Pearson (http://www.cpearson.com/excel/docprop.aspx). However this only worked with DOC and XLS for me, not for DOCX and XLSX.
    1. I think FSO will do what you want

    2. I don't have any experience with DSOfile.dll, but if all you want is to retrieve some properties, the FSO approach would be the way I'd do it
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    Thanks for providing the info.

  15. #15
    For info, it seems that Windows 7 has issues with reading XLSX and DOCS.
    Link.

  16. #16
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    I had a go but as mentioned above could only get them by opening the file with:

    Sub LoopFiles()    
        Dim fWB As Workbook, newRow As Long
        Dim fPath As String, ext As String
        Dim fl As String
             
        fPath = "C:\Users\A\Desktop\From\"
        ext = "*.xlsm"
        fl = Dir(fPath & ext)
        newRow = 2
    
        Do While fl <> ""
            Set fWB = Workbooks.Open(fPath & fl)
            Sheet2.Range("A" & newRow).Value = ActiveWorkbook.BuiltinDocumentProperties("title")
            Sheet2.Range("B" & newRow).Value = ActiveWorkbook.BuiltinDocumentProperties("subject")
            fWB.Close
            fl = Dir
            newRow = newRow + 1
        Loop
    
    End Sub
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  17. #17
    I couldn't quite get it to work.
    Tried replacing ActiveWorkbook.BuiltinDocumentProperties with fWB.BuiltinDocumentProperties but still not quite.
    The other thing is that it does not account for Word files.
    Cheers.

  18. #18
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    If i'm honest it was more of a sample than a solution.

    I was thinking of a different approach,

    How about using an workbook_close event to store your info to an external source then using excel to pull the data for you as needed?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  19. #19
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    For example if you placed this in a module in each document you wish to export these values for (Word or Excel):

    Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As LongPrivate Declare Function GetPrivateProfileInt Lib "kernel32" Alias "GetPrivateProfileIntA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal nDefault As Long, ByVal lpFileName As String) As Long
    Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpString As String, ByVal lpFileName As String) As Long
    
    
    Private Const conFig = "C:\Users\A\Desktop\Test.ini" ' location of ini file
    
    
    Public Function GetINIString(ByVal sApp As String, ByVal sKey As String) As String
        Dim sBuf As String * 256
        Dim lBuf As Long
        lBuf = GetPrivateProfileString(sApp, sKey, "", sBuf, Len(sBuf), conFig)
        GetINIString = Left$(sBuf, lBuf)
    End Function
    
    
    Public Function WriteINI(ByVal sApp As String, ByVal sKey As String, ByVal sValue As String) As String
        WritePrivateProfileString sApp, sKey, sValue, conFig
    End Function
    Then you could place this in the ThisDocument part for word:

    Private Sub Document_Close()     
        x = WriteINI(ThisDocument.Path & "\" & ThisDocument.Name, "Title", ThisDocument.BuiltInDocumentProperties("title"))
        y = WriteINI(ThisDocument.Path & "\" & ThisDocument.Name, "Subject", ThisDocument.BuiltInDocumentProperties("subject"))
    End Sub
    or this in the ThisWorkbook module of a workbook:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        x = WriteINI(ThisWorkbook.Path & "\" & ThisWorkbook.Name, "Title", ThisWorkbook.BuiltinDocumentProperties("title"))
        y = WriteINI(ThisWorkbook.Path & "\" & ThisWorkbook.Name, "Subject", ThisWorkbook.BuiltinDocumentProperties("subject"))
    End Sub
    and it will build an ini file as these books are closed.

    Just something i was thinking about, may not be viable for your needs.

    Hope this helps
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  20. #20
    Thanks very much for the assistance, however I have a large number of files and can't write the code in post #19 in each one of them.
    Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •