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.
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
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 Conn.Open 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 Conn.Close
…note the “If Not” clause to avoid errors potentially caused when the field is null or nonexistent in the SQL table.