export to excel - monthly resource issues (2000)

[AngeP]

I am creating a chart in Excel with details of which projects / tasks that a resource is working on. e.g. from now til the end of the year the training dept has 3 trainers working on project A, 2 on project b and 1 on project C, but project C finishes at the end of October. This is a master project plan with approx. 50 smaller tasks linked to it.
I have taken over the start and finish dates to Excel, but this will only show me totals. I need to break it down to show for October we have 6 trainers working on 3 project, November and December should show 5 trainers on 2 projects.
I can't find a column for Month?
The Task Usage report was helpful, but I can't seem to add the project names to this - nor can I send this to Excel.
If you know how to get the monthly breakdown into Excel, or know how to send the task usage report to Excel I would appreciate your help.

[GARYPSWANSON]

There is no easy way to export timephased data to excel for analysis. The export maps give you a lot of data, but not timephased.

If using the Task or Resource usage view, you could cut and paste the data to excel. (You have to do this twice, once on each side and then make sure the data lines up.) But this is a little clunky. Let me see if I can find some code to help you out.

[GARYPSWANSON]

The following code exports timephased data to excel. Just create a macro in Project and insert this code. Then run it.

<pre>Sub ExportWorkData()

' Export Work Data Macro obtained from
' Special Edition using MS Project 2000
' by Tim Pyron, et al.

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlrng As Excel.Range
Dim Proj As Project
'Dim T As Task
Dim R As Resource
Dim A As Assignment
Dim TSV As TimeScaleValues
Dim Weeknumber As Integer
Dim DatesAdded As Boolean

'Set xlApp = GetObject(, "Excel.Application")

If xlApp Is Nothing Then
Set xlApp = New Excel.Application
xlApp.Visible = True
End If

AppActivate "Microsoft Excel"

Set xlbook = xlApp.Workbooks.Add

Set xlrng = xlApp.ActiveCell

Set Proj = ActiveProject
xlrng = "Weekly Work Report for " & Proj.Name
xlrng.Range("A2") = "As of " & Format(Date, "mmmm dd yyyy")
xlrng.Range("A1:A2").Font.Bold = True
xlrng.Font.Size = 14

Set xlrng = xlrng.Offset(3, 0)

xlrng = "Task Name"
With xlrng.EntireRow
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
.AutoFit
End With

xlrng.EntireColumn.ColumnWidth = 35
Set xlrng = xlrng.Offset(1, 0)

For Each T In Proj.Tasks
' For Each A In T.Assignments
' MsgBox A.ResourceName
' Next


If Not (T Is Nothing) Then
xlrng = T.Name
Set TSV = T.TimeScaleData(Proj.ProjectStart, Proj.ProjectFinish, pjTaskTimescaledWork, pjTimescaleWeeks, 1)

If DatesAdded = False Then
For Weeknumber = 1 To TSV.count
xlrng.Offset(-1, Weeknumber) = TSV(Weeknumber).EndDate
Next
xlrng.Offset(-1, 0).EntireRow.NumberFormat = "mm/dd/yyyy"
DatesAdded = True
End If
For Weeknumber = 1 To TSV.count
xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60
Next

If T.Summary = True Then
xlrng.EntireRow.Font.Bold = True
End If
Set xlrng = xlrng.Offset(1, 0)
End If
Next

xlrng.Offset(-1, 0).CurrentRegion.Offset(1, 0).NumberFormat = "0.00h;;"

End Sub
</pre>

[LanceG]

Gary, I've been playing around with this code and keep getting an error at the following line...
xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60

I get type mismatch. I'm wondering - what is the intent of that line... what are you attempting to insert in to the cell at that point?

[GARYPSWANSON]

The intent of that line is to get the hours data associated with a resource and write that data for the particular task and timeframe. If there are no resources associated with a task, then TSV(Weeknumber) = "" and you can't do the calculation ""/60.

Insert the following if then statement around the statement giving you an error to bypass this.

<pre>If TSV(Weeknumber) <> "" Then
xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60
End If</pre>



HTH

[LanceG]

OK, that makes sense though I did something a little different.

If TSV(Weeknumber) = ""
xlrng.Offset(0, Weeknumber) = "no data"
Else
xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60
end if

[GARYPSWANSON]

Did the code get what you needed to excel?

Out of curiousity, does the "no data" give you an error when you execute the code.

[LanceG]

The code got what I needed to Excel. Though I'm second guessing myself on inserting "No data" it might be redundant.

I don't get an error when executing the code.

Thanks!