Export Resources via VBA

[stuart block]

I am truely stuck on the following piece of code.

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
Dim wrk1 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
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
If wrk = "" then
wrk1 = 0
else
wrk1 = wrk / 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

 

[John]

Stuart,
See my comment at the failure point

I am truely stuck on the following piece of code.

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
Dim wrk1 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
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

The above line has a couple of errors. First a colon is missing from the
timescaleunit (i.e. it should be timescaleunit:=). Second, the "wrk"
object is actually a collection of values from "stat" to "stat+7".
Therefore the ".value" property doesn't exist for the collection. What I
normally do is set up a loop after the TimeScaleData Method to extract
each value of the collection. The easiest way I have found is to use the
Count property to bound the loop. For example:
For j = 1 To wrk.Count
[your variable name] = wrk(j).value
[do whatever with this value]
Next j

Hope this helps.
John
Project MVP

 

[Jan De Messemaeker]

Hi there,

There is another misundertstanding.
All dates in Project are expressed in minutes so your numweeks variable does
not express the number of weeks but the number of 7 minutes time slices
(could be hundreds if not thousands)
Use the Application.DateDifference method.

Hope this helps,

 

[John]

Hi there,

There is another misundertstanding.
All dates in Project are expressed in minutes so your numweeks variable does
not express the number of weeks but the number of 7 minutes time slices
(could be hundreds if not thousands)
Use the Application.DateDifference method.

Hope this helps,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
+32-495-300 620

Jan,
Yeah that's what I thought also, but darned if the value does seem to
work out. I was also dubious about "stat + 7" being one week later than
"stat" but darned if it isn't. I didn't look into the reason why.

John

 

[JackD]

This is NOT true. Dates are not in minutes. Durations are in minutes. Dates
are dates. Adding 1 to a date adds 1 day.
--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html


..

 

[Jan De Messemaeker]

Sorry!

 

[JackD]

No problem Jan. I just didn't want people to get the wrong idea here.

 

[stuart block]

Thanks for the assistance, but eventually I found out that the start and
finish dates in the timescaledata command need to be in a string(text) format
rather than as a date.

Once I changed the variable type to a string it worked perfectly.

So if using the timescaledata command and using a variable for the start and
finish dates, make sure that they are in a string(text) format not as a date.

Thanks again
Stuart