×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using DSOfile

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!

-Josh

CODE

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
with: "User defined type not defined"

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

RE: Using DSOfile

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?

RE: Using DSOfile

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

RE: Using DSOfile

(OP)
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.

RE: Using DSOfile

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources