Results 1 to 7 of 7
  1. #1
    roaftech is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65

    Set file external properties using VBA?


    First, let me clarify that this question concerns the external file properties - the ones that show up in File Explorer and similar tools.
    Context:
    I have a form which generates a report using a selection of sub-reports generated by queries. The report preparation process also sets the report's caption, which is then used by an automated version of PDFCreator to write a pdf copy of the report. This all works fine with no further intervention on my part. File Explorer shows the file in the correct directory with the correct filename, date etc. but I would also like to be able to set the file "subject" property from within the routine. The required text would be derived from info already in the database. Is this possible? If so, any hints as to how?
    TIA, and happy new year!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please provide a sample of what you mean by Subject with respect to File Explorer.
    Happy New Year.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I just added Subject column to File Explorer and it is empty.

    See if this helps https://stackoverflow.com/questions/...ties-using-vba

    Also review https://social.msdn.microsoft.com/Fo...ails-using-vba
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    roaftech
    I believe you mean extended file properties.
    You can read these using VBA - see this example database https://www.access-programmers.co.uk...ile+properties
    NOTE: Subject is extended property 22 but I've never seen it used in any file type

    However AFAIK you cannot write to most extended properties using VBA.
    The old dsofile.dll only works in 32-bit & is, at best, difficult to use anyway.
    The Code Project file in June's first link may be worth investigating - thanks for finding that June
    Also check out the two further links in post #7 of my link above

    EDIT:
    I've just checked the 64-bit dso.dll available from https://www.codeproject.com/Tips/111...ot-Use-DSOfile
    Unless I'm being very dense it still just reads extended properties as the VBA reference library name suggests: DSO OLE Document Properties Reader 2.1
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Its official - I was being dense!

    I have managed to successfully set two extended properties using VBA - Title & Subject
    BUT it only worked for Word/Excel & PowerPoint files.

    Click image for larger version. 

Name:	SetExtFileProperties.PNG 
Views:	22 
Size:	26.4 KB 
ID:	40578

    The code has no effect on e.g. PDF, ACCDB, MDB or txt files

    This was my approach:
    1. Download the appropriate bitness of dso.dll from https://www.codeproject.com/Tips/111...ot-Use-DSOfile and register it
    2. Add the VBA reference library DSO OLE Document Properties Reader 2.1
    3. Create a new module and add the following code:

    Code:
    Option Compare Database
    Option Explicit
    
    'requires the VBA reference: DSO OLE Document Properties Reader 2.1
    'This is the file c:\Windows\System32\dso.dll (Use 32-bit or 64-bit version as appropriate)
    
    Private Function SetFileTitle(ByVal strFileName As String, strTitle As String) As String
    
    On Error GoTo Err_Handler
    
       Dim objFileProp As Object
       'Set oOleDocProp = New DSOFile.OleDocumentProperties
       Set objFileProp = CreateObject("DSOFile.OleDocumentProperties")
    
       objFileProp.Open (strFileName)
        objFileProp.SummaryProperties.Title = strTitle
        objFileProp.Save
        SetFileTitle = objFileProp.SummaryProperties.Title
        
         objFileProp.Close
        
         If Not objFileProp Is Nothing Then Set objFileProp = Nothing
    
    Exit_Handler:
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & " in SetFileTitle procedure :  " & Err.Description
        Resume Exit_Handler
    End Function
    
    
    Private Function SetFileSubject(ByVal strFileName As String, strSubject As String) As String
    
    On Error GoTo Err_Handler
    
       Dim objFileProp As Object
       'Set oOleDocProp = New DSOFile.OleDocumentProperties
       Set objFileProp = CreateObject("DSOFile.OleDocumentProperties")
    
        objFileProp.Open (strFileName)
        objFileProp.SummaryProperties.Subject = strSubject
        objFileProp.Save
        SetFileSubject = objFileProp.SummaryProperties.Subject
        
       objFileProp.Close
        
       If Not objFileProp Is Nothing Then Set objFileProp = Nothing
    
    Exit_Handler:
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & " in SetFileSubject procedure :  " & Err.Description
        Resume Exit_Handler
        
    End Function
    I tested this with code similar to the following:

    Code:
    Sub TestFileSubject()    Dim strText As String
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\ExtFilePropsHelp.docx", "Word doc Subject Info") 'works
        Debug.Print "Subject = " & strText
        
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\Testfile.xlsx", "Excel Subject Info") 'works
        Debug.Print "Subject = " & strText
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\Testfile.pptx", "PowerPoint Subject Info") 'works
        Debug.Print "Subject = " & strText
        
        'These all do nothing
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\ExtFilePropsHelp.pdf", "PDFSubject Info") 'failed
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\Version.txt", "Text file Subject Info") 'failed
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\FilePropertyViewer\FilePropertyViewer.accdb", "ACCDB Subject Info") 'failed
        strText = SetFileSubject("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\FilePropertyViewer\FilePropertyViewer.mdb", "MDB Subject Info") 'failed
      
    End Sub
    
    Sub TestFileTitle()
        Dim strText As String
        strText = SetFileTitle("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\ExtFilePropsHelp.docx", "Word Title") 'works
        strText = SetFileTitle("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\Testfile.xlsx", "Excel Title") 'works
        'Debug.Print "Title = " & strText
        strText = SetFileTitle("G:\MyFiles\ExampleDatabases\ExtendedFileProperties\Testfile.pptx", "PowerPoint Title") 'works
        'Debug.Print "Title= " & strText
    End Sub
    Hope that all helps someone
    The code can I'm sure be improved further

    Other extended properties that could be set using the DSO library reference include Author, Comments, Company, Version etc

    I know the OP wanted to do this for PDF files in which case I don't have a solution. Sorry

    The MS article at https://docs.microsoft.com/en-gb/off...ument-property states:
    The Dsofile.dll sample file is an in-process ActiveX component for programmers that use Microsoft Visual Basic .NET or the Microsoft .NET Framework. You can use this in your custom applications to read and to edit the OLE document properties that are associated with Microsoft Office files, such as the following:
    • Microsoft Excel workbooks
    • Microsoft PowerPoint presentations
    • Microsoft Word documents
    • Microsoft Project projects
    • Microsoft Visio drawings
    • Other files that are saved in the OLE Structured Storage format
    Unfortunately I've no idea which other files are saved in the OLE Structured Storage format
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    roaftech is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65
    Thank you all for sharing your time and expertise.
    I have had a look at the suggested links and sample code, and have looked at a number of file-types by way of experimentation. It appears that these extended properties were available only for those listed by Isladogs above. As it was only a 'nice to have' rather than a more important part of my project I will consider the case closed if not solved.
    Thank you for your help.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by roaftech View Post
    I have had a look at the suggested links and sample code, and have looked at a number of file-types by way of experimentation. It appears that these extended properties were available only for those listed by Isladogs above.
    Not quite true.
    I believe most/all Windows files have those extended properties but values can only be set using VBA for the small number of file types listed in my post and the MS article.
    The values can of course by set by right clicking on other files in Explorer.

    Anyway, not a problem,.
    At least I learned how to do it myself ... but whether I'll ever use that knowledge is another matter
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import External Text File
    By 5pac3m0nk3y in forum Access
    Replies: 1
    Last Post: 04-18-2017, 11:40 AM
  2. Open external program and file using VBA
    By bid19123 in forum Programming
    Replies: 2
    Last Post: 09-21-2016, 02:15 PM
  3. External File Pointing Problem
    By justphilip2003 in forum Access
    Replies: 7
    Last Post: 03-25-2013, 10:29 PM
  4. Help File from Properties?
    By Heatshiver in forum Access
    Replies: 2
    Last Post: 05-06-2012, 08:10 AM
  5. How to select from MDB file through external tools
    By ryanlcs in forum Programming
    Replies: 1
    Last Post: 08-10-2011, 11:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums