Using DSOfile
Using DSOfile
(OP)
Quote (handleman):
Here's some code out of an Excel sheet I used to update all custom properties from an old job to a new job. I wrote this as a one-time shot, so it's pretty brute-force, and could use more comments. Basically, it uses a text file as input for the names of the files to edit. It then searches each custom property (regardless of its name) for specific strings and replaces them with different ones. I had it dump the custom prop name, old value, and new value to the Excel sheet so I could do a quick visual check.
Of course, for this to work you have do download and register the dll first, and include it in the references.
Hope this helps!
-JoshCODE
Sub DoTheSwap()
Dim PropReader As DSOleFile.PropertyReader
Dim DocProps As DSOleFile.DocumentProperties
Dim CustProp As DSOleFile.CustomProperty
Dim PropVal As String
Dim fso As Scripting.FileSystemObject
Dim sFile As String
Dim sFilesPath As String
Dim sFilesList As Scripting.TextStream
Dim CurPath As String
Dim OldJobNo As String
Dim NewJobNo As String
Dim OldProjName As String
Dim NewProjName As String
Dim OldMcNo As String
Dim NewMcNo As String
Dim OldDrawn As String
Dim NewDrawn As String
Dim CurRow As Long
sFilesPath = "C:\Documents and Settings\tnjbrady\Desktop\out.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set sFilesList = fso.OpenTextFile(sFilesPath, ForReading)
Set PropReader = New DSOleFile.PropertyReader
OldJobNo = "TC04152"
NewJobNo = "TC05083"
OldMcNo = "AS134"
NewMcNo = "AS144"
OldProjName = "STEPPER SUB-ASSY CELL"
NewProjName = "STEPPER SUB-ASSY #2"
OldDrawn = "John Doe"
NewDrawn = "Jim Smith"
CurRow = 1
While Not sFilesList.AtEndOfStream
CurPath = sFilesList.ReadLine
Set DocProps = PropReader.GetDocumentProperties(CurPath)
For Each CustProp In DocProps.CustomProperties
On Error GoTo SKIP
PropVal = CustProp.Value
ActiveSheet.Cells(CurRow, 1).Value = CustProp.Name
ActiveSheet.Cells(CurRow, 2).Value = PropVal
PropVal = Replace(PropVal, OldJobNo, NewJobNo)
PropVal = Replace(PropVal, OldProjName, NewProjName)
PropVal = Replace(PropVal, OldMcNo, NewMcNo)
PropVal = Replace(PropVal, OldDrawn, NewDrawn)
CustProp.Value = PropVal
ActiveSheet.Cells(CurRow, 3).Value = PropVal
CurRow = CurRow + 1
SKIP:
Next
Wend
End Sub
This answer was in a previous thread but I think I should open a new one.
I copied and pasted the above code in a macro, in Excel. I downloaded dsofile from Microsoft site, installed it, registered the dll, checked out the reference in Excel VBA and run it. It stops at
CODE
Dim PropReader As DSOleFile.PropertyReader
Is there anything else I need to do before using DSOfile?






RE: Using DSOfile
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
DS: OLE Document Properties 1.2 Object Library
Microsoft Scripting Runtime
Do you get the context-sensitive auto-complete for DSOleFile? For example, if you start typing
Dim DummyVariable as dso
do the DSOle options show up?
RE: Using DSOfile
This tells Visual Basic that you want to use dsofile.dll in that macro.
Eric
RE: Using DSOfile
I know it's hard to believe. My references are:
Visual Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office 9.0 Object Library
DSO OLE Document Properties Reader 2.0
Microsoft Scripting Runtime
I do get context-sensitive autocomplete for DSOleFile.
RE: Using DSOfile
http://www
RE: Using DSOfile