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.
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