1

I'd like to create a script that creates a set of spreadsheets, 1 for each selected row in an MS project schedule. This is more complex that what I've seen the "export function" do, since the export function treats your MS-Project file like a table and does a table to table conversion.

Here's what I want from the functionality:

  • to be able to select a set of tasks in MS-Project and trigger the function
  • to have the function take 3-5 selected items from the tasks and put them in specific locations in an Excel spreadsheet.
  • Ideally, it would let me give it a template spreadsheet, with strings representing the places I want data to go.
  • The spreadsheet will have multiple tabs, and I'd like to set data in multiple tabs.
  • Each task gets a new spreadsheet in its own file.

I'm glad to write a function to do this, but I have no familiarity with any MS macro programming languages or tricks. I can easily visualize the pseudocode I want, I just don't know if there are APIs out there that will do what I want.

Does anyone have a thought on whether what I want is possible, and if it is, how best to approach it. Please include references to learning resources, if possible.

1 Answer 1

1

Yes, this is possible, but perhaps a lot for someone new to macro programming. The complexity is increased a bit because you are trying to work with both excel and project at the same time - but it is all completely do-able.

There are no API's that already do this shrink wrapped for you, but MS Office has a very rich Document Object model. One of the best resources you have is your 'F1' key. Try recording what you want to do in very small steps and modify the code to make it appropriately generic. The recorder can help you discover the the objects, methods and properties you'll need to become familar with.

The main workhorse object in Project is the Task object. In Excel it is the Range object. You'll need to become familar with both to do what you want. A rough outline:

  1. Start with your selected tasks in project
  2. For each task in your selection:
    a. Create a new workbook
    b. Get the values you want from project
    c. write them to the appropriate range in excel

I'm a bit fuzzy on the template part of your question, so didn't worry about it in the example below, but you can define a thing called a Named Range in excel. So these names could live in your template, you can even reuse the same name on multiple sheets (tabs) if you want and use these names to define where to write your taks data. For example, I wrote task id out to Range("A2"), but it could have also been Range("Project_ID")

Also, I would recommend breaking this up into several functions, one for each step in your outline. Here is a very basic starting point. To use this example, you will need to create a reference to the Excel object definitions from Project. Open the project VBA IDE and select Tools -> References. Find the entry Microsoft Excel XX.0 Object Library and put a check in the checkbox. 'XX' is your excel version. Mine is 10, yours miight be different. Anyone using this would have to make the same selection.

Option Explicit

Sub CopyTasksToExcel()
    Dim xlApp As Excel.Application
    Dim t As Task
    Dim wb As Excel.Workbook

    Set xlApp = New Excel.Application

    For Each t In ActiveSelection.Tasks
        Set wb = CreateWorkbook(xlApp, t.Name, t.ID)
        WriteSheetHeadingOn wb
        WriteTaskOn t, wb
        wb.Close SaveChanges:=True
    Next t

    xlApp.Quit
    Set xlApp = Nothing
End Sub

Function CreateWorkbook(ByVal xlApp As Excel.Application, _
                        ByVal TaskName As String, _
                        ByVal TaskID As Long) As Excel.Workbook
    Dim wb As Excel.Workbook
    Dim fName As String


    Set wb = xlApp.Workbooks.Add            'You could specify a template here
    fName = ActiveProject.Path & "\" _
                               & TaskID & "-" _
                               & TaskName & ".xls"
    wb.SaveAs FileName:=fName

    Set CreateWorkbook = wb
    Set wb = Nothing
End Function

'Writes date from a Project Task to the provided workbook.
Sub WriteTaskOn(ByVal prjTask As Task, _
                ByVal xlWb As Excel.Workbook)
    With xlWb.Sheets(1)
        .Range("A2").Value = prjTask.ID
        .Range("B2").Value = prjTask.Name
        .Range("C2").Value = prjTask.Duration
    End With
End Sub


Sub WriteSheetHeadingOn(ByVal xlWb As Excel.Workbook)
    With xlWb.Sheets(1)
        .Range("A1").Value = "ID"
        .Range("B1").Value = "Name"
        .Range("C1").Value = "Duration"
    End With
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .