I think that you will run into a problem with the format that you are choosing. MS Excel 2003 and below only allows 256 columns. Based on what you laying out, you will only be able to add 177 tasks (1 for ref name) and two columns per task for start and finish dates. MS Excel 2007 will allow you to use 16384, which allows you to have 8,191 tasks in the format that you listed. That being said, here is some code as a start:
Option Explicit
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range
Sub CreateTaskList()
Dim fso As New FileSystemObject
Dim xlApp As Object
Dim xlbook As Object
Dim xlWorkSheet As Object
Dim tsk As Task
Dim Tsks As Tasks
Dim sRef As String
'Create the excel application object
Set xlApp = CreateObject("excel.application")
'Set to true to avoid the display of the sheet being filled in
xlApp.Visible = False
'AppActivate "Microsoft Excel"
xlApp.ScreenUpdating = False
'Create the workbook - creates sheets 1, 2, & 3
Set xlbook = xlApp.Workbooks.Add
'Create a new sheet
Set xlsheet = xlbook.Worksheets.Add
xlsheet.Name = "Tasks"
'Do not allow alert messages to be displayed
xlApp.DisplayAlerts = False
If fso.FileExists("C:\filename.xls") Then
fso.DeleteFile ("C:\filename.xls")
End If
xlbook.SaveAs FileName:="C:\filename.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
'Set Range to write to first cell
Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
dwn 1
xlRow = "Tasks"
dwn 1
'Save the project name ref
sRef = Left(ActiveProject.Name, 3)
Set Tsks = ActiveProject.Tasks
For each tsk in Tsks
If not tsk is Nothing Then
Set xlCol = xlRow.Offset(0, 0)
xlCol = sRef
rgt 1
xlCol = tsk.Name
rgt 1
xlCol = tsk.Start & " Start"
rgt 1
xlCol = tsk.Finish & " Finish"
dwn 1
End If
Next tsk
End Sub
Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub
Sub rgt(i As Integer)
Set xlCol = xlCol.Offset(0, i)
End Sub
You could simply export the data using the save as function. Create a custom field that concatenates the word