none
Get All Timephased Data in XML Export from Project Professional 2013 RRS feed

  • Question

  • Hi All,

    Using VBA to save project file/s as xml, then SQLXML Bulk Load into an SQL Server database. This is the fastest way I've found to get Project data into SQL Server, which we do in order to provide flexible access to the data and link to a range of other business and job-management systems. The Timephased Data is incomplete, however. Some assignments get remaining work and some don't. No assignment actual costs at all.

    Is there a) a setting to make sure all Timephased Data gets exported or b) a good document explaining how the software decides what to include in the export?

    I'll consider it a personal favour if you all resist the urge to advise me to rip into the timescaledata collections with VBA. I've inflicted way more than enough gigantically nested for-next loops on the world already.

    It looks like those Visual Reports send nice data to a .cub file, I know nothing whatsoever about OLAP, does anybody know the fastest way to open one of those (in VBA) and send Timephased Data to a database?

    Thanks

    Marcus

    Wednesday, January 6, 2016 12:59 PM

All replies

  • WolfeLogic,

    You indicate that some assignments get remaining work and some don't and none get actual costs. Right off the bat that tells me your code has some missing elements but without seeing that part of your macro the only response I can offer is to suggest you review the VBA help file on the Assignment.TimescaleData Method. Actual cost data is available for all three types of timescale data (i.e. Task, Resource and Assignment), but I don't see remaining work listed for any of them. Based on that you will have to derive remaining work using work minus actual work.

    John

    Wednesday, January 6, 2016 3:55 PM
  • Thanks Jon,

    The whole line of code to export to xml is as follows:

    Application.FileSaveAs Name:=pr.Path & "\" & PName & " Export.xml", FormatID:="MSProject.XML"

    where pr is Application.Activeproject, PName is a string set previously.

    I've tried specifying values for Format, Backup, ReadOnly, TaskInformation, Filtered, Table, Map, ClearBaseline, ClearActuals, ClearResourceRates and ClearFixedCosts.

    So now maybe we're a bit further away from the bat and you can tell me what's missing from my code. That will be awesome.

    Didn't spend much time trying Maps, there's no Timephased Data at all with maps.

    To be specific about Remaining Work, I'm referring to Timephased Data in the xml with <Type>2</Type> which is "Assignment Remaining Work" according to the documentation contained in the schema definition in the sdk. You're right about the timescaledata collections, there is no such thing as remaining work, you have to use work minus actual work.

    Thanks,

    Marcus

    Wednesday, January 6, 2016 10:49 PM
  • WolfeLogic,

    Okay, my first question. Is your installation of Project 2013 updated with the latest December 2015 Public Update? If not, run Windows Update.

    I don't have Project 2013 but I do have Project 2010 which has the same file format. I created a simple Project file - one task(10 day), one resource($10/hr). I set the task at 50% complete to give an actual cost of $80 and remaining work of 40h.

    I then used the Immediate Window in the VBA editor to execute the following line of code:

    application.FileSaveAs name:="C:\Users\John\Documents\XML Test4.xml", FormatID:="MSProject.XML"

    The following screen shot shows the XML file with actual cost and remaining work highlighted for the assignment. So....I don't know why it's not working for you, but you might try a simple test file as I did and then work from there.

    Hope this helps.

    John

    Thursday, January 7, 2016 6:24 PM
  • I don't think there is any way of controlling the xml output by Project.

    I have code that exports Project data to SQL Server via VBA and does use Timephased data. Only 2 levels of For Next and it runs fast, quicker than a save as to xml. I use OLEDB and INSERT INTO SQL commands.

    Like you I have found problems with xml but the VBA export directly to SQL Server has run reliably for me for many years.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, January 7, 2016 10:29 PM
    Moderator
  • Hi John, thanks, the question is about timephased data. No problem with assignment properties, as far as I have observed so far.

    Regards,

    Marcus

    Friday, January 8, 2016 3:02 AM
  • If you write a good enough Project VBA book the world will only ever want one Project VBA Book!

    I think you're right that there's no way to control the xml output but if I could find good documentation I would be able to predict its behaviour and fill gaps by other means.

    What method did you use to set the start and end dates for your tsv collections? I was using your suggested method for a few years and found that if your system gets a bit complicated, especially setting lots of specific timescaled actuals, that MS Project would seem to get confused and create (for example) pjAssignmentTimescaledWork long after the assignment's Finish, or tiny little values spread all the way back to 1984.

    I'm looking into the data exported from Visual Reports; it looks like it has all those nice WeekTSV and/or DayTSV values that you get in the Project Server reporting tables. MDX. How hard can it be?

    Regards,

    Marcus

    Friday, January 8, 2016 3:39 AM
  • Marcus,

    Sorry, I misinterpreted your post. It looks like our master of programming, Rod, has stepped in with his take on the issue.

    John

    Friday, January 8, 2016 3:58 PM
  • For each Tsk in activeproject.tasks
        For each Assgn in Tsk.Assignments

    Then use the assignment start and finish. You obviously need to test for blank tasks etc. Setting actual hours in Project for a time period is a different problem from just exporting data. Obviously exporting weekly is quicker than daily etc.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Saturday, January 9, 2016 4:37 AM
    Moderator