Checking for Columns when exporting to excel

[as]

I am using some of Jack Dahlgren's code (Posted below with my changes
included) to export to excel the project schedule. When I run the script, any
task more than five levels deep, fails too return any data as set for in the
VBA Code.

Does any7one have any ideas on this, or maybe point me in the right direction.

Jeff
====================================================
'This module contains macros which will export
'tasks to excel and keep the task hierarchy.
'modify as necessary to include other task information

'Copyright Jack Dahlgren, Feb 2002

'===================================== count columns needed
=====================================
ColumnCount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel > ColumnCount Then
ColumnCount = t.OutlineLevel
End If
End If
Next t
'=====================================Set Column Label on Page
===========================
'Set Range to write to first cell
Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
Set xlRow = xlRow.Offset(1, 0)
xlRow = "OutlineLevel"
Set xlRow = xlRow.Offset(1, 0)

'label Columns
With xlRow.EntireRow
.Font.Bold = True
.WrapText = False
.Font.ColorIndex = 2
.ColumnWidth = 10
.HorizontalAlignment = xlHAlignCenter
' .VerticalAlignment = xlVAlighnCenter
.AutoFit
' EntireRow.Interior
' .ColorIndex = 1
.Interior.Color = RGB(0, 0, 0)
Pattern = xlSolid
End With
'=====================================Set Headings on Page
===========================
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
Set xlCol = xlCol.Offset(0, 3)
xlCol = "ID"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Duration"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Work"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Actual Work"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Start Date"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Finish Date"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Baseline Finish"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Actual Start Date"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Actual Finish Date"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Predecessors"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Estimated"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Percent Complete"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Over Allocated"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Deadline"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Finish Variance"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Summary Task"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Milestone"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Leveling Delay"


Set xlCol = xlCol.Offset(0, 1)
xlCol = "Baseline Start"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Baseline Finish"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Current Target"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "MTP Date"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "BAseline"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "KTP Date"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Risks"
Set xlCol = xlCol.Offset(0, 1)
xlCol = "Issues"
Tcount = 0

'================================= Get data
====================================
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Set xlRow = xlRow.Offset(1, 0)
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If

For Each Asgn In t.Assignments
Set xlRow = xlRow.Offset(0, 0)
Set xlCol = xlRow.Offset(0, Columns)
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.ID
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format((t.Duration / 480), 0#)
Set xlCol = xlCol.Offset(0, 1)
xlCol = FormatNumber((Asgn.Work / 480), 0#)
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format((Asgn.ActualWork / 480), 0#)
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(Asgn.Start, "SHORT DATE")
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(Asgn.Finish, "sHORT dATE")
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(Asgn.BaselineFinish, "sHORT dATE")
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(Asgn.ActualStart, "SHORT DATE")
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(Asgn.ActualFinish, "sHORT dATE")
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.Predecessors
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.Estimated
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.PercentWorkComplete
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.Overallocated
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.Deadline
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format((Asgn.FinishVariance / 480), 0#)
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.Summary
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.Milestone
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.LevelingDelay
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(t.BaselineStart, "SHORT DATE")
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(t.BaselineFinish, "sHORT dATE")


Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(t.EnterpriseProjectDate1, "SHORT DATE")
'current Target
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(t.EnterpriseProjectDate2, "sHORT dATE") '
make The Promise
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(t.EnterpriseProjectDate3, "sHORT dATE")
'Baseline
Set xlCol = xlCol.Offset(0, 1)
xlCol = Format(t.EnterpriseProjectDate4, "sHORT dATE")
'Keep The Promise

xlCol = t.EnterpriseProjectText6
Set xlCol = xlCol.Offset(0, 1)
xlCol = t.EnterpriseProjectText7 '
Set xlCol = xlCol.Offset(0, 1)


Next Asgn
Tcount = Tcount + 1
End If
Next t

 

[JackD]

I haven't run it, but it appears likely that you might want to get rid of
the column count part of it. I put that in to write column headings for the
outline level. Also the tcount variable seems to be unnecessary.

It seems that it might be best to start fresh instead since you are
rewriting so much of it.
I think that will probably solve your problem.

 

[wrt]

Jack

Part of the problem I'm finding as I watch the variables go by is that the
project name is different from the project being used to retrieve
information. I saved a copy, closed project, opened the backup copy and ran
the script. It still pulls form the previous version of the project file. IS
there anyway to use ActiveProject to force the change.

Also, the routine seems to be ignoring tasks set as milestones. Any ideas?

Thanks
Jeff

 

[JackD]

Jeff,

Without seeing all the code you are using it is difficult to tell why that
might be happening. One problem I see with milestones is that you are
exporting assignments data right? If there is no resource assigned to the
milestone, then there is no assignment data to export. I would expect that
nothing would be exported.

If you want task data (instead of assignment data) dump the line that says
for each asgn in t.assignments and change the asgn.<whatever> to
t.<whatever>

Like I said, I think you have enough skill to create this thing mostly from
scratch. The most important part is getting the control structures correct
(ie; all the For each's and if's...) If I were writing it I'd probably do
that just to make sure it does exactly what I want.

 

[Buster]

Thanks Jack

Worked like a charm when I deleted the Asng reference, voila all thedetails
came to life!

JEff