Export Resources via VBA.

[stuart block]

Good Morning,

I am trying to Export resources via VBA from a master project schedule to
give me an Artemis style list. eg.

Weekending resource name project hours cost
03-jul-05 FRED PRJ001 30 900
10-jul-05 FRED PRJ001 30 900
17-jul-05 FRED PRJ002 30 900
17-jul-05 SAM PRJ003 40 2000
24-jul-05 SAM PRJ003 40 2000

etc.

I am using MS Project 2000, I know now any SQL.
Thanks, Stuart

 

[Jan De Messemaeker]

Hi Stuart

In VBA, loop through the resources
For each anyman in activeproject.resources
Then find
anyman.timescaledata
with the option per week, both for work and cost
(So far for the simple things)
Now loop through these Timescalevalues (calling them TSV f.i.)

For counter=1 to TSV.Count
You now have TSV.value but what is a bit tricky is to get the value of the
Week ending
I would use the Application.dateadd function

Hope this helps,

 

[stuart block]

Thanks Jan,

I will try and develop this further, any more help would be very grateful,
as my VBA experience is very limited.

Stuart

 

[Jan De Messemaeker]

Hi,

OK, give it a try, call if ever you get stuck.
Greetings,

 

[stuart block]

Thanks for the advice Jan,

This is the code that I have developed but it stops when I try and get the
work figure. Any ideas....

Sub Export1()
Dim t As Resource
Dim stat As Date
Dim xlRow As Excel.Range
Dim wrk As Long

Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Test"
Set xlRow = xlApp.ActiveCell
remDur = 0
stat = ActiveProject.ProjectStart
With ActiveProject
numweeks = (.ProjectFinish - .ProjectStart) \ 7 + 1
End With
For I = 0 To numweeks
For Each t In ActiveProject.Resources

' IT STOPS HERE ***************
Set wrk = t.TimeScaleData(stat, (stat + 7), timscaleunit =
pjTimescaleWeeks).Value / 60

xlRow.Value = t.Project
Set xlRow = xlRow.Offset(0, 1)
xlRow.Value = stat
Set xlRow = xlRow.Offset(0, 1)
xlRow.Value = t.Name
Set xlRow = xlRow.Offset(0, 1)
xlRow.Value = wrk
Set xlRow = xlRow.Offset(1, -3)
Next t
stat = stat + 7
Next I
End Sub

Thanks

Stuart