Month: May 2012

Project VBA and Azure: Better Together

Andrew Lavinsky, 5 Comments

I know what you’re all thinking.  Project 2010 is fantastic – and we can’t wait till we get to see the next version.  The only problem is that there’s this one critical function that Microsoft really needs to bake into the product.  You see, they really need to add a button that will automatically translate my schedule into Romanian.

image

Now, I don’t think we’ll see that quite baked into the product in the near future, but in this post, I’ll show you how to quickly add a bit of VBA that ties into the Microsoft Translator API to automatically translate your project schedule into Romanian – or 36 other supported languages ranging from Hmong Daw to Haitian Creole.  (Although, it still doesn’t support Mongol unfortunately.  I guess I’ll have to wait a bit for that.)

image

I know that I am a bit stuck in a VBA rut, but every now and then, I run into a trick that’s so cool, it’s worth blogging about.  In this case, I can’t really take much credit.  JimS, one of the users on the forums came up with this code to automatically translate task names from one language to another.  (Although as far as I can tell, the code originally derived from a post by Travis McGee back in 2010.)

image

The magic is done through the use of a bit of VBA code – that then calls the Microsoft Translation API, and drops the results into the Text1 field.

image

I had to update the code a bit for this blog as it appears the original code was using an older authentication form that has now been deprecated.  Hence, I would definitely not recommend using the older Bing AppID authentication method  that is floating around on the Web and instead implement the code below which authenticates against the Azure marketplace using an authorization token.

Registering with the Azure Marketplace

The first step though is to get two parameters: the ClientID and the ClientSecret from your free account on the Azure Marketplace.  Check here for guidance on getting those.  These parameters link the application to your user account – so for example, you could use the free version which is limited to 2 million characters/month, or pay a bit more to get more volume.

The VBA

Hat tip to this post for the guidance on adding the functionality to get the Authorization Token.

You’ll need to enter your personal Client ID and the Client Secret into the code to get it to function.  All the configuration parameters are grouped in the first function.

'Sources of info:
'http://blogs.msdn.com/b/translation/
'http://msdn.microsoft.com/en-us/library/hh454950.aspx
'https://datamarket.azure.com/dataset/1899a118-d202-492c-aa16-ba21c33c06cb
'http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/e4c149c4-fefb-48fd-8990-db6a8f0f9045

'Set the Following References :
'Microsoft XML, v6.0

Sub TranslateTaskNames()

'This macro will translate task names and drop the new values into the Text1 field.

'Set the authorization token parameters here. Macro won't work without the appropriate values.
'See here for instructions on getting set up with the appropriate codes.
Dim ClientID As String
Dim ClientSecret As String

ClientID = "ENTER YOUR CLIENT ID HERE"
ClientSecret = "ENTER YOUR CLIENT SECRET HERE"
'Refer to this page for language codes: http://msdn.microsoft.com/en-us/library/hh456380.aspx
Dim LanguageFrom As String
Dim LanguageTo As String
LanguageFrom = "" 'Set the source language. Leave blank to auto-detect.
LanguageTo = "FR"

'Give the user the option to change the target language
LanguageTo = InputBox("Enter the target language code:", "Target", LanguageTo)

'The next section performs the translation

Dim T As Task
Dim tName As String

For Each T In ActiveProject.Tasks
    tName = MicrosoftTranslate(T.Name, LanguageFrom, LanguageTo, ClientID, ClientSecret) 'Translate the Task Name
    tName = Mid(tName, 2, Len(tName) - 2) 'Truncate quotation marks from result set
    T.Text1 = tName
Next T

'The next section displays a confirmation box

Dim tCompleted As String
tCompleted = MicrosoftTranslate("Translation Completed", LanguageFrom, LanguageTo, ClientID, ClientSecret) 'Translate confirmation message
tCompleted = Mid(tCompleted, 2, Len(tCompleted) - 2) 'Truncate quotation marks from the result set

MsgBox "Translation Completed / " & tCompleted, vbOKOnly, "Done"

End Sub

Function MicrosoftTranslate(sText As String, LanguageFrom As String, LanguageTo As String, ClientID As String, ClientSecret As String) As String

'This function calls the Microsoft Translate API

Dim ID As String
Dim sURL As String
Dim oH As MSXML2.XMLHTTP
Dim sToken As String

   ID = ""    ' Bing appID deprecated in Dec 2011 in favour of Access Token. Use nothing for legacy appID parameter
   sURL = "http://api.microsofttranslator.com/V2/Ajax.svc/Translate?oncomplete=&appId=" & ID _
            & "&from=" & LanguageFrom & "&to=" & LanguageTo & "&text=" & sText
   sToken = GetAccessToken(ClientID, ClientSecret)
   Set oH = CreateObject("MSXML2.XMLHTTP")
   oH.Open "POST", sURL, False
   oH.setRequestHeader "Authorization", "Bearer " & sToken
   oH.send
   MicrosoftTranslate = oH.responseText
   Set oH = Nothing
End Function

Function GetAccessToken(ClientID As String, ClientSecret As String) As String

'This function authenticates against the Azure marketplace to ensure the user is authorized to use the app.

Dim webRequest As MSXML2.ServerXMLHTTP
Set webRequest = CreateObject("MSXML2.ServerXMLHTTP")
Dim URI As String
URI = "https://datamarket.accesscontrol.windows.net/v2/OAuth2-13"

Dim sRequest As String
sRequest = "grant_type=client_credentials" & _
      "&client_id=" & ClientID & _
      "&client_secret=" & URLEncode(ClientSecret) & _
      "&scope=http://api.microsofttranslator.com"

Dim mtToken As String
Set webRequest = New ServerXMLHTTP
webRequest.Open "POST", URI, False
webRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
webRequest.send sRequest
mtToken = webRequest.responseText
Set webRequest = Nothing

'Debug.Print mtToken

Dim arr As Variant, header As String
header = "{""access_token"":"""
arr = Split(mtToken, ",")
GetAccessToken = Mid$(arr(0), Len(header) + 1, Len(arr(0)) - Len(header) - 1)
End Function

Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String

'This function modifies the text to properly work in a URL.

Dim StringLen As Long
 StringLen = Len(StringVal)
 If StringLen > 0 Then
   ReDim result(StringLen) As String
   Dim i As Long, CharCode As Integer
   Dim Char As String, Space As String
   If SpaceAsPlus Then Space = "+" Else Space = "%20"
   For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
      result(i) = Char
      Case 32
      result(i) = Space
      Case 0 To 15
      result(i) = "%0" & Hex(CharCode)
      Case Else
      result(i) = "%" & Hex(CharCode)
      End Select
   Next i
   URLEncode = Join(result, "")
End If

End Function

Set the source and/or target language per the options here.

Here’s the same exercise from English to Spanish.  I don’t make any claims about the quality of the translation, but overall, it doesn’t seem like a bad start.

image

Kind of wish I’d had something like this back in the late 90s when I had to figure out a system to translate Fluke testing reports into English.

image

Project VBA and Azure: Better Together

Revisiting Project Trend Analysis and Project Change Logs

Andrew LavinskyLeave a comment

The question always arises as to how to best take a snapshot of project data, and use that in timephased reports or change logs.  In fact, by my count, this is the third time I’ve addressed this topic in this blog:

  1. Using External Content Types and SharePoint lists.
  2. Using VBA to store data in spare baseline fields.
  3. Using VBA to store data in a custom SQL table (keep reading).

In this blog post, I’d like to take a different approach than in the past and show how to use VBA to post snapshots of project data to a SQL database.  The value of storing the data in a custom SQL table of course is that the data is available on the enterprise level, subject to enterprise retention policies, and available for use with enterprise reporting solutions.

Now, I could always use automatically generated queries.  In fact, it would be quite simple to set up a timed job that runs within SQL every Friday evening and takes a snapshot of project data.  As I’ve mentioned before, I am not a big fan of such automated queries – generally because I know a lot of project managers who like to catch up on work over the weekend – or who often will submit the numbers only to immediately realize that they forgot to totally update their schedule, make adjustments and then resubmit.  Running a timed snapshot process may not work well for folks like this.

My preference is for the project manager to manually trigger that snapshot in time as part of the routine status reporting cycle.  You could even incorporate some workflow or alerts around the data when it’s submitted as part of the report.

Creating the Table

In this case, I’ll just use some code that Calin, one of our developers created for me to support some EVM reporting.  This query will generate a table within a SQL database.   Generally, you would add this table to a custom database.

CREATE TABLE [dbo].[ProjectStatusSnapshots](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Date] [datetime] NOT NULL,
      [ProjectName] [nvarchar](500) NOT NULL,
      [ProjectUID] [uniqueidentifier] NULL,
      [ProjectStatusDate] [datetime] NULL,
      [ProjectACWP] [money] NULL,
      [ProjectBCWP] [money] NULL,
      [ProjectBCWS] [money] NULL,
      [ProjectEAC] [money] NULL,
      [ProjectCPI] [money] NULL,
      [ProjectSPI] [money] NULL,
      [ProjectTCPI] [money] NULL,
      [ProjectCost] [money] NULL,
    [ProjectActualCost]  [money] NULL,
      [ProjectBaseline0Cost] [money] NULL 
CONSTRAINT [PK_ProjectStatusSnapshots] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Modify the fields to fit your needs.  You might also consider creating two tables, one for project header information, and one for task information.

Confirm that we have a new table in SQL.

image

Adding the VBA

The VBA then is relatively simple.  We just need to create a bit of code to generate the database connections and then run a SQL update query to insert the right data into the database table.

Note that I am grabbing the ProjectUID as well.  This will facilitate reports that will join the trend analysis data with the default Project Server reporting database.

As I mentioned above, you may want to consider adding code to capture task level metrics for trend analysis.  That would pretty much be the same concept.  Just make sure to grab a unique identifier for the tasks to use in generating reports.  Using that technique and a field to capture the current user, you could effectively implement a change log – especially if you embed the VBA code in the Project OnSave activity.

Sub UpdateSQL()

    'This macro will log specific field data into a customized SQL database.

    'Create connection to SQL table.'

    Dim Conn As New ADODB.Connection

    Dim SQLInstance As String
    Dim SQLDatabase As String

    'Set the connection parameters here

    SQLInstance = "Demo\Demo" 'Enter the name of the SQL instance
    SQLDatabase = "zzz_TrendAnalysis" 'Enter the name of the SQL database

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

    If Conn.State = 1 Then 'To avoid errors where the connection was opened and not closed
        Conn.Close
    End If

    'Open the connection and assign the data to the table.

    Conn.Open

    With ActiveProject.ProjectSummaryTask

    Conn.Execute ("INSERT INTO " & SQLDatabase & ".dbo.ProjectStatusSnapshots([date], ProjectUID, ProjectName, " _
    & "ProjectStatusDate, ProjectACWP, ProjectBCWP, ProjectBCWS, ProjectEAC, ProjectCPI, " _
    & "ProjectSPI, ProjectTCPI, ProjectCost, ProjectActualCost, ProjectBaseline0Cost) " _
    & "VALUES(GetDate(), " _
    & Chr(39) & ActiveProject.GetServerProjectGuid & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.Name & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.StatusDate & Chr(39) _
    & Chr(44) & .ACWP _
    & Chr(44) & .BCWP _
    & Chr(44) & .BCWS _
    & Chr(44) & .EAC _
    & Chr(44) & .CPI _
    & Chr(44) & .SPI _
    & Chr(44) & .TCPI _
    & Chr(44) & .Cost _
    & Chr(44) & .ActualCost _
    & Chr(44) & .BaselineCost & ")")

    End With

    Conn.Close

    MsgBox "Project snapshot has been saved.", vbOKOnly, "Confirmation"

End Sub

Run the VBA a couple of times to confirm it’s generating data.

image

Once you’ve validated the VBA, consider adding it as a button to a customized ribbon toolbar.  This will give your users the ability to trigger the macro directly from the main Microsoft Project interface.

Retrieving the Data

From there, it’s just a simple question of retrieving the data.  As I mentioned above, I fully expect the PM to potentially create multiple snapshots at each status period, I may only want to grab the “final” one that was posted each day.  To do that, we just need to modify a basic SQL query.

Here’s an example:

SELECT dbo.ProjectStatusSnapshots.Id, dbo.ProjectStatusSnapshots.Date, dbo.ProjectStatusSnapshots.ProjectName, dbo.ProjectStatusSnapshots.ProjectUID, dbo.ProjectStatusSnapshots.ProjectStatusDate, dbo.ProjectStatusSnapshots.ProjectACWP, dbo.ProjectStatusSnapshots.ProjectBCWP, dbo.ProjectStatusSnapshots.ProjectBCWS, dbo.ProjectStatusSnapshots.ProjectEAC, dbo.ProjectStatusSnapshots.ProjectCPI, dbo.ProjectStatusSnapshots.ProjectSPI, dbo.ProjectStatusSnapshots.ProjectTCPI, dbo.ProjectStatusSnapshots.ProjectCost, dbo.ProjectStatusSnapshots.ProjectActualCost, dbo.ProjectStatusSnapshots.ProjectBaseline0Cost, DailyRecord.LastRecord FROM dbo.ProjectStatusSnapshots RIGHT OUTER JOIN (SELECT MAX(Id) AS LastRecord FROM dbo.ProjectStatusSnapshots AS ProjectStatusSnapshots_1 GROUP BY ProjectUID, CAST(Date AS Date)) AS DailyRecord ON dbo.ProjectStatusSnapshots.Id = DailyRecord.LastRecord

Run this query to generate a table of the last entry posted per project on any given day.  You could then use the ProjectUID field to join this date to live data in the Project Server reporting database.

Revisiting Project Trend Analysis and Project Change Logs

Incorporating Project Server Data into Word Documents

Andrew Lavinsky4 Comments

This is a topic I’ve briefly played with before – how to automatically incorporate Project Server data into Office documents.  The REST API has long been the go to tool for doing such things, as the API allows us to consume Excel reports dynamically in Word or Powerpoint documents.

image

The general goal in adding this sort of document automation is to attempt to drive user behavior a bit.  We want our users to move away from generating documents on their C drive and generating them within SharePoint.  Adding document automation is on of the major incentives to do so, as it makes it easier to create a document within SharePoint than on the local drive.

In this post, I want to take that integration to the next level, i.e. I want to incorporate Project Server fields within the text itself of a Word document.  I might do this to automate some of my status reporting, or to create a project charter template that automatically populates with key data. 

Now, I’ll point out that this technique is not quite seamless, and probably could benefit from a little custom SharePoint development….but hopefully this may give you some ideas, and be referenced in other, more robust solutions.  I also haven’t quite figured out how to deploy this across multiple sites without custom development.  So for now, take the solution for what it is, a rough idea for automating documents at the workspace level.  If someone smarter than I wants to take this idea and run with it….by all means, be my guest.

Building the External Content Type

The first step in accomplishing this is to build an external content type (instructions here).  When building the external content type, make sure to add a filter on the project name (instructions here).

Building the Document Template

Now let’s go create our document template.  To do this we need a Project Workspace.  It doesn’t have to be a live project workspace as really all we need is a document library.  We’re going to add our ECT to the document library as a custom field.

First, navigate to the Library Settings.

image

Scroll down and add a new column.

image

I then configure the new column as below – using the name of the ECT that I created in one of the previous posts.

image

Now let’s associate that field with the default content type.  To do this, we need to enable content type management within the document library.  Click on the advanced settings to do this.

image

Enable content type management for the library.

image

Confirm that the field has been associated with the default document content type.  Once we’ve done that, lets create a new document from the workspace.  (You could also take an existing document template and load it to the document library and then adapt it to our purposes.) 

image

Note how the Document Information Panel now shows a number of blank fields.

image

These fields will be populated from the project header data in the document library.  More importantly, we can consume those fields within the document using the Quick Part mechanism.

image

I’ll go ahead and create a quick status report leveraging the Quick Parts.

image

Now let’s save the document back to the workspace.  For good measure, I’ll save another copy off to my desktop as a template file.

Once the file has been posted back to the SharePoint site, test it out by selecting the option to Edit Properties.

image

Type the project name and hit the search button….

image

…and the metadata is now populated.  Open the document to see the changes.

image

Yes….I know it’s kind of annoying to have to open the document and then close it and edit the metadata column to get it to work properly.  I am sure there’s a decent solution to that, but for now, I’ll leave it there.

Associating the Document Template

The next step here might be to associate our validated document template with the content type on the workspace.  To do that, I go back into our document library and upload the template to the content type.

image

…and now I can create a document within the document library, save it, add the metadata and know that everything is properly updated.

As I said above, the entire process is perhaps a bit contrived, but hopefully this should serve as a launching point for ideas.  Other options that might be a bit easier (depending on your particular skill set) would be to simply write VBA within Word to query Project Server data and populate fields correctly.

Also, consider combining this technique with the concept of document sets.  In that case, you would only have to apply the fields once, and then have all of the changes trickle down to the document set components.  Please see this post from Alex Burton on leveraging document sets with Project Server: http://epmsource.com/tag/document-sets/.

Credits

Note that this post was inspired by (yet another) highly informative and entertaining presentation from Mr. Tom Resing, now of Rackspace.  His presentations have become a highlight of the annual Houston SharePoint Saturday event.

Update (5/11): Tom’s presentation was indeed informative and entertaining, but after looking at my notes, Mike Huguet, PFE Extraordinaire, was the one who actually presented on customizing Word documents at the SharePoint Saturday event.  Just wanted to clear that confusion up.  Now I can rest at night.

Incorporating Project Server Data into Word Documents

Calculating Elapsed Duration

Andrew LavinskyLeave a comment

I’ve been using so many of Microsoft Project’s approximately 900 functions for so long, that often times, I forget that some of the functions I take for granted are often undiscovered to long time users of the software.  In the last couple of months, at least twice, I’ve found end users to be unaware of the elapsed duration functionality.  Hence, I figured I’d write up a quick blog post about it.

Observing Elapsed Duration

First off, what is elapsed duration?  If you open Microsoft Project and enter “1ed” in the Duration column, you’ll see duration calculated as an elapsed day.

image

Essentially, it appears to be pretty much the same as a 1 day task.  Let’s calculate 7 elapsed days.

image

You’ll see that elapsed duration ignores the weekend non-working time and schedules the task over the weekend.

Now, let’s assign work resources to further illustrate the difference…

image

You’ll see that Task 1 is calculating per the normal duration calculation, i.e. 7 days X 8 hours per day = 56 hours.  Task 2 is actually calculating at 7 days X 24 hours per day = 168 hours.

Elapsed duration calculates a continuous 24 hour cycle and ignores non-working time.

Use Cases

So now we’ve defined elapsed duration.  When should we use this to model activities in our schedule?  Typically, you would use elapsed duration to model automated testing routines.  For instance, I might start a test on Friday at 5PM, and then let it run over the weekend.

Similarly, I might use elapsed duration to model tasks like data conversions.  I can kick the data conversion off before leaving for the day, then let the task run for 12 hours.  I am sure the potential uses are endless.

Last but not least, I know Dale Howard was recently noodling on a similar topic.  Check out his blog post for an overview of how to detect tasks using elapsed duration.

Calculating Elapsed Duration