VBA: Updating Assignment Level Fields from SQL

Rod Gill’s book on VBA for Project 2007 has been sitting on my bookshelf for a couple of years.  I finally decided to open it up again last week to see if I could use it to sort out some issues for a client prototype.

This post, and probably some upcoming posts will focus on specific issues I ran into that I was able to resolve, but that didn’t appear in the search engines at first glance.  In this post, I’ll talk about how to merge SQL level data into a Microsoft Project plan. 

There’re a lot of examples on the Web and in Rod’s book on how to add assignments and tasks from SQL, but it took a bit of finding to determine how to merge SQL data with the specific assignment in Project.

The Challenge

In this case, we have a pretty typical challenge.  The invoiced amount for a specific cost code is kept in a LOB application.  Whenever the user decides to update the schedule, they also need to import the invoiced amount, and use that as part of their status reporting metrics.

The Set Up

There’re a couple of ways of skinning this cat.  The way I decided to go about it was to create a unique ID for each of the assignments in the schedule.  I did this by concatenating a custom field, ProjectID and the Resource Initials for the resource.

Why Resource Initials, you ask?  Well, Resource Initials are one of the five or so fields that appear in the Resource Pool, but that also appear as task level data.  Off of the top of my head, the other fields that work in this way include the Resource Name, Resource Group (which I was using elsewhere), Resource Phonetics, and well, I think that’s pretty much it.

For the Project ID, I created a project level enterprise custom field in Project Server, then created a second task field called Project_ID_Calculated that equaled the Project ID and rolled down to the assignment level.

The assumption by the way, is that each resource is assigned once and only once in the project.

At the end of the day, this gives us a unique ID for each assignment, something I am storing in the Assignment_ID field.  Feel free to use your own field to assign a unique ID as works for you.

Creating the Assignment ID

Once I have that set up, populating the Assignment_ID field is a matter of including the following code in my macro.

    'Update Assignment ID as the concatenation of Project ID Calculated and Resource Initials'

    Dim T As Task
    Dim A As Assignment

    For Each T In ActiveProject.Tasks
        For Each A In T.Assignments
            A.Assignment_ID = A.Project_ID_Calculated & "." & A.Resource.Initials 'Assign new Assignment ID
        Next A
    Next T


Querying SQL

Assuming I have a SQL database up and running with data corresponding to these Assignment IDs, I then have to run the following code to create the connection. (Tip of the hat to Rod Gill for instructions in his book.)

    'Create connection to SQL table.'

    Dim Conn As New ADODB.Connection

    Conn.ConnectionString = "Provider=sqloledb;" _
    & "Data Source=SQLInstanceName;" _
    & "Initial Catalog=Staging;" _
    & "Integrated Security=SSPI;"

…and the following code to query the database and update the Project plan.

    Dim SQLInvoiced as String


    For Each T In ActiveProject.Tasks
        For Each A In T.Assignments
            If Not Conn.Execute("SELECT Invoiced From dbo.SQLTable WHERE [AssignmentID] = " & A.Assignment_ID).EOF Then '...only for codes found in the db
                    SQLinvoiced = Conn.Execute("SELECT Invoiced From dbo.SQLTable WHERE [AssignmentID] = " & A.Assignment_ID).GetString 'Gets the actual data
                    A.Invoiced = SQLInvoiced 'Update Invoiced custom field for resource assignments
            End If
        Next A
    Next T


…note the “If Not” clause to avoid errors potentially caused when the field is null or nonexistent in the SQL table.

VBA: Updating Assignment Level Fields from SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s