« Free PMBOK Download - This nonsense thing | Main | Waiting for Google »

MS Project VBA Programming - Writing project properties to a file

Microsoft Project files have a couple of different types of properties. The first are the "BuiltinDocumentProperties" which Project inherits from the Office Suite (Word, Excel, Access ...). These properties describe things like author, title, subject, creation date and the like. The second are the CustomDocumentProperties which are specific to Project. You can also create your own custom properties as well. The custom properties can be used to report cost, duration, start and finish dates of the project and a multitude of other interesting items.

One problem that this causes is that it can sometimes be difficult to know what properties are being used in a specific file. To report them out and to give a simple example of writing to a text file I put together a macro which loops through all of them and writes them to a file. Even if you are not interested in the project properties this example illustrates some of the more commonly required programming techniques. I'll walk through the code with some comments and then offer the whole thing at the end if you want to cut and paste.

Sub writemyproperties2()

Dim MyString as String
Dim MyFile As String
Dim fnum as Integer
Dim myIndex As Integer
Dim myProj As Project
Dim skipme As Boolean
skipme = False
Set myProj = ActiveProject

Declaring the variables is optional in Project VBA, but it can help prevent some problems later. If a variable isn't defined then Project treats it as a "variant" which it needs to allocate more memory for. Project also makes some assumptions about how to treat a variant in different circumstances. In most cases it assumes correctly, but there is always the chance that it may make the wrong assumption so it is good practice to be explicit about your variables. Following this we set the initial values for some of the variables.

The next step is to set up a file to write to. The next bit of code sets the file name and then uses the FreeFile() method to create a file. We open the file to write to it. We choose to open the file "for output". This allows us to write to it. The other possible modes are "for input" which would allow us to read data from the file and "for append" which appends data to the file. This last mode is useful for logs.

MyFile = "c:\" & ActiveProject.Name & "_properties.txt"
fnum = FreeFile()
Open MyFile For Output As fnum

Once we have created and opened the file we can write to it. In the "for output mode we do this with a "Write" statement. First we write a line as a header and then a blank line. Each "write" statement creates a new line. The comma is required.

Write #fnum, "Built In Properties"
Write #fnum,

Now we use a "For...Next" loop to go through all the properties. Because there are some gaps in the numbering of the properties, the macro would fail when it hits gaps in the sequence. We solve this by putting in an error handler. When the code errors then it goes to the code which we want to execute "On Error"

For myIndex = 1 To myProj.BuiltinDocumentProperties.Count
skipMe = False
On Error GoTo ErrorHandler
MyString = (myIndex & _
": " & _
myProj.BuiltinDocumentProperties(myIndex).Name & _
": " & _
myProj.BuiltinDocumentProperties(myIndex).Value)
If skipMe = False Then
Write #fnum, MyString
End If
Next myIndex

You can see that each loop through will write the property number, the name and the value of the property. You may be wondering why the if...then" statement is there. I include it because of the way the errors are handled. Our error code is very simple. It just resumes on the next statement. Since the next statement writes a line to the file it would result in re-writing the previous property again, so we add a line to the error handler which sets skipMe to true and then we do not write the property.

From here the code for the custom document properties is the same.

Write #fnum, "-----------------------------------------------"
Write #fnum,
Write #fnum, "Custom Properties"
Write #fnum,
For myIndex = 1 To myProj.CustomDocumentProperties.Count
skipMe = False
On Error GoTo ErrorHandler
MyString = (myIndex & _
": " & _
myProj.CustomDocumentProperties(myIndex).Name & _
": " & _
myProj.CustomDocumentProperties(myIndex).Value)
If skipMe = False Then
Write #fnum, MyString
End If
Next myIndex

We need to close the file after this.

Close #fnum

And finally we have the code to handle errors and end the procedure.

ErrorHandler:
skipMe = True
Resume Next
End Sub

Here is the complete macro to cut and paste

Sub writemyproperties2()
'This macro exports all the built-in and custom project properties
'to a text file. It lists the index of the property, the name and the value.
'It demonstrates the use of a simple error handler to skip the errors that
'occur when a property is not defined or used.

'Copyright Jack Dahlgren, Nov 2005

Dim MyString as String
Dim MyFile As String
Dim fnum as Integer
Dim myIndex As Integer
Dim myProj As Project
Dim skipme As Boolean

Set myProj = ActiveProject
skipMe = False

'set location and name of file to be written
MyFile = "c:\" & ActiveProject.Name & "_2properties.txt"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'write project info and then a blank line
Write #fnum, "Built In Properties"
Write #fnum,

For myIndex = 1 To myProj.BuiltinDocumentProperties.Count
skipMe = False
On Error GoTo ErrorHandler
MyString = (myIndex & _
": " & _
myProj.BuiltinDocumentProperties(myIndex).Name & _
": " & _
myProj.BuiltinDocumentProperties(myIndex).Value)
If skipMe = False Then
Write #fnum, MyString
End If
Next myIndex
Write #fnum, "-----------------------------------------------"
Write #fnum,
Write #fnum, "Custom Properties"
Write #fnum,

For myIndex = 1 To myProj.CustomDocumentProperties.Count
skipMe = False
On Error GoTo ErrorHandler
MyString = (myIndex & _
": " & _
myProj.CustomDocumentProperties(myIndex).Name & _
": " & _
myProj.CustomDocumentProperties(myIndex).Value)
If skipMe = False Then
Write #fnum, MyString
End If
Next myIndex
Close #fnum

ErrorHandler:
skipMe = True
Resume Next

End Sub

RELATED POSTS

Comments (3)

pasan:

Write #fnum
Error given as variable not define

chening wangmo:

i would like to requst for the features of project writing

Jorge:

I need convert field Number2 with mask (for example) 1.244,45 or 1.477,00 and field Number3 (for example) with mask 25,33% or 43,00%... Using Visual Basic for Application into MS Office Project...

It's Possible?

Please, excuse me english it's not very good...

Jorge

-------------------
Number fields can not be formatted with a mask. If you want formatted numbers you can put them into a text field. For example if I had a value in Number2 of 43.45 I'd use a formula in Text2 which would take that number and append a % sign, -JD

Post a comment

(Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

About

The previous article is Free PMBOK Download - This nonsense thing.

The next article is Waiting for Google.

Current articles are in the main index page and you can find a complete list of articles in the archives.

Creative Commons License
This weblog is licensed under a Creative Commons License.
Powered by
Movable Type 3.34