PS2007 Project Details (VBA)

Fourth post, I think this is it.  We started with C#, what you normally see the Project Server samples in.  We shifted to VB.Net and realized the difference wasn’t too significant.  Then we moved into JavaScript, which proved to be challenging.  Finally we are moving into VBA/VB6.

The sample I wrote is for VBA in Excel.  I had requests for both VB6 and VBA.  I decided that VBA would get across the point of using the Visual Basic syntax, without having to struggle to get a UI up.  I used the JavaScript sample as the starting point, and ported from there.  There was a bunch of syntactic changes (duh!), but the logic stayed the same.  I replaced writing the data into tables with writing them to worksheets.  To load the sample up in Excel, open the macro editor and add the attached file to it. 

How does the sample work?  First update the server name to reflect your server.  Then run the GetProjectList macro.  It will populate the current sheet with Project names and Guids.  Select the cell of the Guid for the Project you want to see details on, run the DisplayProjectDetails macro.  A new sheet will appear with all the Project detail information.

There isn’t really anything here that isn’t in the JavaScript sample… using XMLHTTP, consuming datasets with XMLDOM, and traversing the DOM to load up the cells.  I did write a neat routine to determine if a string is a Guid (kind-of a waste of time seeing I could have done the same thing in .Net using regular expressions in less than half the code.)

Just because I did this in Excel doesn’t mean you are tied to there.  You could easily move this to Project client.  For that matter you could use the JavaScript sample for some Project Guide work.  I hope you find these four samples helpful in you comparisons.


Comments (2)

  1. Rod Gill says:

    Hi Larry,

    Thanks for this VBA example. I’ve tried on two different Project Server 2007 instances and I can’t get the code to work. Firstly I set a reference to Microsoft XML, v6.0 and got the code compiled. When I ran the GetProjectList sub in both systems I get a "Object variable or With block variable not set" error on line:

    Set projects = xmlObj.childNodes(0).childNodes(0).childNodes(0).childNodes(1).childNodes(0).childNodes

    I single stepped thru the code and no errors were thrown, but xmlObj is still = Nothing, hence the error. No errors were thrown either in the SoapRequestWorker function.

    Can you help please as this is code I would very much like to get working?

    Many thanks,

    Rod Gill

  2. Rod Gill says:


    I’ve copied your VBA code to Excel in teh Microsoft Virtual PC image and added a reference to Microsoft XML 6.0 so the code compiles successfully. The image was edited to allow internet access.

    However the code is failing at line:

    Set xmlObj = xmlDoc.DocumentElement

    As xmlobj is set to nothing.

    Any ideas on why please?