John,
I could get that code to work if I wanted some values from task fields, but
it wouldn't give me the resource values I am looking for. Here is the full
code for the macro I am trying to run. This is using my code not your
suggestion.
***************************************************
Sub WKCT_Load()
Dim tsk As Task
Dim tsv As TimeScaleValues, hmany As Long, HoursPerDay As String, hdr As
String
Dim strt, fnsh, seldt, badID
hdr = "Category;Resource Group;Job;Date;Hours"
' sets report start and finish dates based on current date
Select Case Format(Date, "mm")
Case 1 To 11
Select Case Format(Date, "dd")
Case 1 To 24
strt = Format(Date, "mm") & "/1/" & Format(Date, "yyyy")
Case 25 To 31
strt = Format(Date, "mm") + 1 & "/1/" & Format(Date, "yyyy")
End Select
Case 12
Select Case Format(Date, "dd")
Case 1 To 24
strt = Format(Date, "mm") & "/1/" & Format(Date, "yyyy")
Case 25 To 31
strt = Format(Date, "mm") + 1 & "/1/" & Format(Date, "yyyy") + 1
End Select
End Select
Select Case Format(Date, "mm")
'if month is prior to September then Finish Date defaults to end of this
fiscal year
Case 1 To 8
fnsh = "03/31/" & Format(Date, "yyyy") + 1
'if month is September or later then Finish Date defaults to end of next
fiscal year
Case 9 To 12
fnsh = "03/31/" & Format(Date, "yyyy") + 2
End Select
' opens text file for output
Open "c:\data\text\WorkCtr.txt" For Output As #2
Print #2, hdr ' prints header information
'generate report
For Each tsk In ActiveProject.Tasks
If tsk.ResourceGroup = "" Or tsk.ResourceNames = "103300" Or
tsk.ResourceNames = "P-103300" Then
Else
Set tsv = tsk.TimeScaleData(strt, fnsh, TimescaleUnit:=pjTimescaleDays)
For hmany = 1 To tsv.Count
badID = tsk.ID
If tsv(hmany).Value = "" Then
Else
Print #2, tsk.Text3 & ";" & tsk.ResourceGroup & ";" & tsk.Text1
& ";" & Format$(tsv(hmany).StartDate, "mm/1/yy") & ";" & tsv(hmany).Value / 60
End If
Next hmany
'StatusBar True, "Outputing Records"
End If
Next tsk
Close #2
'open Excel and create pivot table
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "C:\Documents and Settings\knewman\Application
Data\Microsoft\Excel\XLStart\PERSONAL.XLS"
XL.Visible = False
XL.Application.Run "PERSONAL.XLS!Project_Data.Work_Center_Load" '
creates table
XL.Application.Run "PERSONAL.XLS!Module15.wchtml" ' creates web page
XL.Application.Quit
Set XL = Nothing
End Sub
**********************************************
I tried replacing my "For Each tsk In ActiveProject.Tasks.....Next tsk"
statement with your code but I got the same result.
In either case the "If tsk.ResourceGroup = "" Or tsk.ResourceNames =
"103300" Or tsk.ResourceNames = "P-103300" Then" statement is where it bombs.
Any help would be appreciated.
Kevin
John said:
I have a master project that used to consist of subprojects as ile items
within the same file. Hence I could use the following in my VB coding:
"For Each tsk In ActiveProject.Tasks"
Now, in place of these line items, the subprojects are actually inserted
projects that use resources from the same pool and my code bombs. I get the
error <Object variable or With block variable not set> when I open the macro
in debug mode.
What do I need to do to get it working again?
I need to run a report weekly that shows the resource usage across all
projects within the master project by month
Kevin,
I'm not sure why your code bombs, I would expect it to not give what you
want but otherwise it should run ok. The error message sounds like the
code is trying to refer to an object that isn't defined.
Nonetheless, when working with consolidated projects a different tack is
needed to cycle through all the tasks. Remember, the subtasks of the
inserted projects are NOT part of the master because the subproject are
not part of the master - they are only inserted via a pointer. Here is a
looping structure you can use to look at all tasks in a master. The
first line insures the master is "exploded":
OutlineShowTasks expandinsertedprojects:=True
SelectTaskColumn
Set Area = ActiveSelection.Tasks
For Each t in Area
[your code here]
Next t
Hope this helps.
John
Project MVP
