Original Post Date: Thursday, March 29, 2012

Introduction

The new TruePlanning COM Application Programming Interface (API) found in the 2012SR1 release of TruePlanning provides a powerful mechanism for leveraging the power of TruePlanning within custom solutions. Through the COM API TruePlanning projects can be created, updated, calculated and saved allowing for near limitless potential for integration with TruePlanning.  That said it is an “API” which means some programming will need to be done. This discussion is focused on how to get started using the TruePlanning COM API.

Development Environments

The TruePlanning COM API is written in C++, but is available to any programming environment that can work with COM.  Such environments include Visual Studio for the .NET languages, unmanaged C++, java and Visual Basic for Applications (VBA).

For the purposes of this discussion, VBA from Excel will be used as it is the most ubiquitous development platform in Windows. Anyone with Excel can make use of the TruePlanning API and Excel is the most common application users need to integrate with TruePlanning.  It is also very forgiving and requires the least experience to start creating amazing integrations with TruePlanning.

Creating a TruePlanning project through VBA

The following steps will prepare the Excel VBA environment to use the TruePlanning COM API (TPCOM), create a project, and then save that project.  Excel 2007 is the version of Excel being used in this discussion. If a different version is being used some of the screen shots may look different, and some of the set up steps will be different, but the code is shown will work.

So let’s get going creating an integration between TruePlanning and Excel!

  1. Launch Excel.
  2. Open a new workbook.
  3. Using “Save As”, save the workbook as an “Excel Macro-Enabled Workbook (*.xlsm)” file.
  4. Display the “Developer” ribbon. To do this:
    1. Click the “Windows” button (round button on the top left of the Excel window, with the multicolored Windows icon).
    2. Click the “Excel Options” button at the bottom of the Excel Windows menu.
    3. Check the “Show Developer tab in the Ribbon” checkbox.  (See screen shot following this set of instructions.)
    4. Click Ok.

  1. Select the “Developer Ribbon

  1. Click the “Insert” ribbon button. This will display a set of controls that can be added to the Excel sheet.
  1. Select the “Command Button” from the ActiveX Controls section of the Insert menu.  It will be the top left item in the bottom half of the Insert menu.
  1. Your cursor will become a “+”. Click somewhere on Sheet1 and drag to size the command button. Release the mouse button. As this is VBA, the bigger the button the better!

  1. Double click the button to create a macro. This will launch the Microsoft Visual Basic for Applications design environment.  You will see code has been created to handle the event of the button being clicked.

  1. Create a reference to the TruePlanning COM object.  In order to use the TruePlanning API, the current code base needs to be told that it is going to be used. To do this we will create a reference. In other programming environments this process will be a bit different, but the result will be the same. In Excel the following steps will create the reference:
    1. Click the Tools menu in the Microsoft Visual Basic for Applications design environment

b.      Click the References option. This will launch the references dialog.

c.       Locate the “TruePlanningApi 12.1 Type Library” and check the checkbox in front of it to select it.

d.      Click OK.

  1. View the TPCOM object in the “Object Viewer”.  The Object browser is a great resource for learning about the objects in the TPCOM API. To use it follow the following steps:
    1. Select the “View” menu in the Microsoft Visual Basic for Applications design environment.
    2. Select the “Object Browser” options, or you can just press “F2”.
    3. Use the pull down in the top left of the Object Browser window to select “TruePlanningApi”.  (See following screen shot.

  1. The Object Browser has three panes. The top left pane shows the objects in the API and the top right shows methods and properties of the object selected in the left pane.  In the above screenshot the “Cost Object” object is selected and its methods and properties are displayed in the right pane.
  1. At the bottom of the Object Browser window the third pane contains details about the currently selected method or property. In the above screen shot the arguments for the “New” method of the “Cost Object” object are seen. 
  1. Click the “X” button on the top right of the Object Brower window to close it, or double click on the “Sheet1 (sheet1)” entry on the left most pane of the VBA design environment. This will return focus to the code for the button that has been created.

  1. Create an instance of the TruePlanning application:
    1. The first step in using the TruePlanning API is to create an instance of the TruePlanningApi.Application object.
    2. Add the following code:

   Dim app As TruePlanningApi.Application

   Set app = New TruePlanningApi.Application

  1. The above code defines a TruePlanning Application variable and instantiates it (gives it life).

  1. Create a TruePlanningApi.Session object.
    1. The TruePlanningApi.Session object represents an open TruePlanning application. To open TruePlanning a connection must be made to the database. The TruePlanning application uses connection information stored in the registry. When using the TruePlanning API the connection name must be provided. The TruePlanningApi.Application object can supply a list of available connections. In this case we’ll use the default connection name.
    2. Declare and instantiate a TruePlanning.Session object. The TruePlanningApi.Application’s “Login” method takes the name of a connection and returns a TruePlanning.Session object.
    3. Add the following code:

   Dim ses As TruePlanningApi.Session

   Set ses = app.Login("(local)")

  1. Test the code that was added for validity by using the “Debug->Compile VBA Project” menu option.
  1. Create a new project.
    1. A TruePlanningApi.Project object represents a TruePlanning project. It contains all of the components of projects found in TruePlanning including Cost Objects, Worksheet Sets, Local Escalations, a country, and a note.
    2. New projects are created from the collection of projects maintained by the TruePlanningApi.Session object. They are returned from the “New” method on the TruePlanningApi.Projects collection.  The “New” method requires at least a name for the project. Optional arguments are a country and an escalation set.
    3. Declare and instantiate a TruePlanning.Project object:

   Dim proj As TruePlanningApi.Project

   Set proj = ses.Projects.New("MyNewProject")

  1. Calling the New method on the Projects collection will create a new TruePlanning project in memory. Note: that that at this time the project has not been saved so if the code were to stop executing at this point, the project would no longer exist.
  1. NOTE: If the provided project name is a duplicate of a project name that already exists for the user currently executing the code, the TruePlanning API will rename the project to make the project name unique. It is important to understand this because this can lead to unexpected behavior, particularly if there is code that is assuming a project name.  It is a good idea to check the project name after the project has been created.

 

  1. Display the name of the newly created project:
    1. Display the name of the newly created project by displaying it in a message box by adding the following code:

   MsgBox (proj.Name)

  1. Save the project:
    1. Save the project by adding the following code:

   proj.Save

  1. Close the project:
    1. When a project is no longer needed the project needs to be closed. It is important to note that closing a project does not cause the project to be saved automatically. It is up to the programmer to ensure projects are saved before being closed. Any changes to a project that were made since the last save will be lost if a project is closed before it saved. Projects that were never saved will be lost for good.
    2. Projects that are not closed are left in a locked state. This can happen when code crashes. If a project is left in a locked state the only way to release the lock is to use the Project Manager dialog in TruePlanning, right click on the locked project and select the “Release Lock” right-click menu option.
    1. To close a project call the “Close” method on the TruePlanningApi.Project by adding the following code:

proj.Close

  1. Close the Session.
    1. Closing the session is like closing TruePlanning. The connection to the database is closed. All memory used by TruePlanning is released. Just as it is important to close a project, it is also important to close the session.
    2. To close a session call the “Logout” method on the TruePlanningApi.Session object by adding the following code:

ses.Logout

  1. Complete the code by ensuring all memory is released.
    1. It is a good practice to ensure as much memory has been release as possible. In VBA to do this, all variables that make use of the “Set” keyword need to be set equal to “Nothing”. So in this example, the app, ses, and proj variables were set equal to TruePlanning API objects using the “Set” keyword and therefore need to be set equal to “Nothing” after they are no longer needed, which in this case is the end of the click event.
    2. Use the following code to accomplish this:

   Set proj = Nothing

   Set ses = Nothing

   Set app = Nothing

 

  1. Test the Code:
    1. Select the first line in the CommandButton1_Click method.
    2. Press the “F5” key.
    3. This will run the code as if the button that was created had been clicked.
  1. Debugging the code:
    1. To debug the code, place debug points in the code by clicking in the grey bar to the left of the code window:

  1. When the code is executed and the line with the breakpoint is hit, the code will stop execution and the VBA development environment can be used to examine the code.
  1. Pressing “F8” will advance the code, stepping into any functions that are called.
  1. Pressing “Shift-F8” will advance, but will not step into functions or subroutines.
  1. Pressing “F5” will cause the code to continue running until another breakpoint is hit or until the code complete.
  1. Test this by clicking on the command button that was created. Immediately after the button is pressed the VBA development button should come into focus with the line of code with the breakpoint highlighted in yellow.
  1. Complete Code:
    1. The complete code should look as follows:

 

Private Sub CommandButton1_Click()

   Dim app As TruePlanningApi.Application

   Set app = New TruePlanningApi.Application

   Dim ses As TruePlanningApi.Session

   Set ses = app.Login("(local)")

   Dim proj As TruePlanningApi.Project

   Set proj = ses.Projects.New("MyNewProject")

   MsgBox (proj.Name)

   proj.Save

   proj.Close

   ses.Logout

   Set proj = Nothing

   Set ses = Nothing

   Set app = Nothing

End Sub

Conclusion:

In the end the above steps are not particularly complicated.  After working with the above code. the use of the TruePlanningApi Application, Session, and Project objects will become second nature. The above steps, while detailed, really take minimal time to set up and allow users to perform very powerful tasks with TruePlanning.  Questions can be sent to help@pricesystems.com.

Thank you,

PRICE Systems L.L.C.