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:
- Start with your selected tasks in project
- 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