Using DSOfile

[dogarila]

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!

-Josh

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

Dim PropReader As DSOleFile.PropertyReader

with: "User defined type not defined"

Is there anything else I need to do before using DSOfile?

 

[handleman]

I don't think so. Here are the references that are checked on my machine (in order)

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?

 

[EEnd]

In the Visual Basic editor window for your macro, select Tools -> References. Then find and chek “DSO OLE Document Properties Reader 2.0.”. Then hit ok.

This tells Visual Basic that you want to use dsofile.dll in that macro.

Eric

 

[dogarila]

handleman,

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.

 

[EEnd]

The problem is that the format of the calls to dsofile changed between 1.2 and 2.0. Some examples in the new format can be found at:

http://www.microsoft.com/technet/community/columns/scripts/sg0305.mspx

 

[handleman]

I wonder if something's changed between DSOfile 1.2 and 2.0? If they've renamed the PropertyReader object as something else it would cause problems. The only other screwy thing that sometimes happens to me is that VBA has trouble getting references right. For example, I sometimes have to specify VBA for basic string functions in the code, such as VBA.Right(), VBA.Chr() instead of just Right() or Chr(). Try moving the DSOfile reference up in the list as high as it'll go. That sometimes helps.