Automating a Report

[Dawn Crosier]

My manager wants the Who Does What Report to print each Resource on a
separate page. So my thought was to create a macro that would step through
all the ID's using the report setup with a Resource Range.

I can call the report from code, but I don't know how to answer the Resource
Range dialog box.

Can someone provide some suggestions?

Thanks.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
How about,
For Each Res in ActiveProject.Resources

John

 

[Dawn Crosier]

John -

For Each Res in ActiveProject.Resources will loop me through the report for
all my resources, but it does not fill-in the prompt for the ID. If I
change the report so that it does not prompt for the Resource Range, then I
get the complete report repeated for the number of resources that I have.

I need to get to some code that will allow me to present the report for 1
resource at a time. Does that help explain my problem better?

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
I assume this is an update from your other post in the .developer group.
For reference there is no need to cross post. Those of us who respond
check all newsgroups but sometimes one of us will spend time answering a
question only to later find out that someone else has already answered
the question in another group.

It sounds like you are using the Reports Method to pop up the Reports
dialogue box for each resource, but rather than me trying to figure out
what you code looks like, can you post it or e-mail it to me? It might
facilitate suggesting a solution.

John

 

[Dawn Crosier]

John -

My post in the other group is an alternative plan. If I can't get this
method to work, I would like to figure out a method to create a custom
report from scratch.

My code is very simply as follows. I have the Resource Range set because I
have gone into the Report dialog box and set it as an option. So, if you
could direct me in how to set it as I call the report, that would be great.

Sub WhoDoesWhatReport()
'Dim R As Resource

'For Each R In ActiveProject.Resources
ReportPrintPreview Name:="Who Does What"
'Next R

End Sub

Thanks in advance for your assistance.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
Ok, I understand you reason for the second post.

The simplest answer is to change your one line macro to be"
ReportPrint Name:="Who Does What"

As long as you have already set the report filter for 'Resource Range',
right after the 'printing xxx' message appears, another dialogue will
appear asking for the Resource Range.

It is indeed possible to use VBA to create custom reports and the whole
process can be automated to simply hitting a menu selection or custom
toolbar button. I do it all the time. However the macros I create
generally are for customized data and formatting that is not available
through the custom reports wizard. I find it much easier to either pull
or generate the Project data needed and then export it to Excel where it
can be formatted in any way desired.

Hope this helps.
John

 

[Dawn Crosier]

John -

What I really need is the ability to automatically print one Who Does What
report for each resource and have that copy be resource specific.
Therefore, your suggestion of using For Each R in Resource is exactly what I
want to use, but I want to be able to loop through the Resource Range rather
than create a copy of the Who Does What report for all 44 resources.
Therefore, I need to be able to expose the Resource Range input boxes and
fill them automatically.

Thanks for helping me.


--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
The last response I gave will allow you to put a Resource report for
each resource on a separate sheet but it is very tedious and virtually
no better than doing it manually. I was just trying to go with your flow.

Let's try a more automated approach, one that doesn't use the Reports
feature at all because the Resource Range is a filter not an object. It
therefore cannot be "exposed" other than for direct user input.

The 'who does what' report is essentially a variation of the Resource
Usage view with the timescale data left off. I suggest you create a
custom Resource Usage view that has the Resource fields you wish to
show. Drag the horizontal divider bar to the extreme right so the
timescale data is not shown. Then use the following macro to set page
breaks for each resource and print.

Sub WhoDoesWhatReport()
ViewApply Name:=[your custom Resource Usage view name here]
SelectResourceColumn
OutlineHideSubTasks
'eliminate any unassigned tasks
FilterEdit Name:="EachRes", taskfilter:=False, Create:=True,
overwriteexisting:=True, _
FieldName:="ID", Test:="is greater than or equal to", Value:="1", _
showinmenu:=False, showsummarytasks:=False
FilterApply Name:="EachRes"

For i = 1 To ActiveProject.Resources.Count
SelectRow Row:=i, rowrelative:=False
PageBreakRemove
PageBreakSet
Next
OutlineShowAllTasks

FilePrint

End Sub

There are other ways to get the report you want using VBA but this
seemed the simplest although it took me a while to figure out how to do
it.

John

 

[Dawn Crosier]

John -

Thanks for the help. I think that this is very close to what will work for
my manager, however, for some reason the page breaks are not effective. I
am using Project 2003 and even though the page breaks are in the view, they
are not working in Print or PrintPreview.

Is that something that you would know about?

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
To be honest, I didn't actually print a page and unfortunately my
printer is not working at the moment so I can't test it. You might try
modifying the 'FilePrint' line to the following (it should be the
default but I also know VBA and page breaks are funky sometimes).
FilePrint pagebreaks:=True

I also ran into another problem. The original code has the line:
'PageBreakRemove' just before the page breaks are set. I added this line
to ensure page breaks were being set, not reset. However when I ran the
code today, I got a runtime error on the 'PageBreakRemove' line. To
counter this I modified the macro as follows:

Sub WhoDoesWhatReport()
ViewApply Name:="resource usage"
SelectResourceColumn
OutlineHideSubTasks
FilterEdit Name:="EachRes", taskfilter:=False, Create:=True,
overwriteexisting:=True, _
FieldName:="ID", Test:="is greater than or equal to", Value:="1", _
showinmenu:=False, showsummarytasks:=False
FilterApply Name:="EachRes"

On Error Resume Next
For i = 1 To ActiveProject.Resources.Count
SelectRow Row:=i, rowrelative:=False
PageBreakRemove
PageBreakSet
Next
OutlineShowAllTasks
On Error GoTo 0

FilePrint PageBreaks:=True

End Sub

If the code still doesn't work for you, maybe someone else can step in
and enlighten us both. As a backup, try setting the page breaks manually
and see if that works any better.

As a last resort, a more extensive VBA macro can be written to export
the data to Excel and have it formatted to be on separate Worksheets.

Sorry for the hassle.
John

 

[Dawn Crosier]

John -

Thanks for the code update. I had tried inserting the page breaks manually,
with no effect, so I am sure that this is a "bug" that we have found. I can
not find anything in the Microsoft knowledge base about it though.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
I got my printer working but unfortunately not so that I could test the
macro (I have an emulated printer and . . . it's a long story). I did
however modify the 'FilePrint' statement to just print two sheets of my
test file output. When I hit print, the print window said that it was
sending sheets 1 and 2 to printer port LPT1, just as expected. That
tells me the code is working and doing what is intended. Therefore I
wonder if the problem is with your printer setup. When you say you tried
inserting the page breaks manually and it had no effect, what exactly
did you get for output, all resources on a single (or multiple) pages?
Can you try printing to a different printer? If you insert manual page
breaks into a Gantt Chart view, does the printer recognize them?

If all else fails (but I've got to believe there is a solution for the
existing problem), we can take an approach that I know will work.
Namely, export the data to Excel and print from there.

John

 

[Dawn Crosier]

John -

Thanks so much for all your help. I ended up setting up an export to Word.
It has a quirk in it, in that occasionally it hangs up and can not locate
the document it just created to put the details in it. However, closing
out of Project fixes the problem.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[John]

Dawn,
I'm glad you got something working. You didn't say exactly how you do
the export (i.e. save as a text file or through VBA). I've never
exported to Word using VBA, only to Excel or PowerPoint but I know each
app has its own little quirks with regard to working with other apps via
automation.

John

 

[Dawn Crosier]

John -

I am exporting through VBA. I am having a intermittant error with the
"Remote Server" being unavailable, - error 462; but I am close.

If you are interested - here is my code so far:

Option Explicit
Dim appWord As Word.Application
Sub WhoDoesWhatReportbyCode()

Dim R As Resource ' Resource object used in For Each loop
Dim Rname As String ' Resource name
Dim RID As Long ' Resource ID
Dim txtReportBody As String
Dim aAssign As Assignment
Dim txtResourceInfo As String
Dim strReportTitle As String
Dim txtReportDetails As String
Dim appWordDoc As Word.Document
'Dim appWordRng As Word.Range

RID = 0

Application.StatusBar = "Currently compiling resource information."

'Loop through the resources in the project and then each assignment for each
'of the resources. For each Assignment that is not finished gather data and
'place it into a variable along with the same infor from the other
assignments.

For Each R In ActiveProject.Resources
txtReportBody = ""
txtResourceInfo = R.ID & vbTab & R.Name & vbCrLf
For Each aAssign In R.Assignments
If aAssign.ActualFinish = "NA" Then
With aAssign
'This next part creates the details of the report
txtReportBody = txtReportBody & vbTab & .TaskID & vbTab
& _
.TaskName & vbTab & Format(.Start, "mm/dd/yyyy") & vbTab
& _
Format(.Finish, "mm/dd/yyyy") & vbCrLf
End With
End If
Next aAssign
txtResourceInfo = txtResourceInfo & txtReportBody & "~~"
txtReportDetails = txtReportDetails & txtResourceInfo & vbCrLf

Next R

'Open Word
Err.Number = 0
On Error GoTo NotLoaded
Set appWord = GetObject(, "Word.Application.8")
NotLoaded:
If Err.Number = 429 Then
Set appWord = CreateObject("Word.Application.8")
Debug.Print Err.Number
End If

appWord.Visible = True
Debug.Print Err.Number
'On Error GoTo 0


'Place in Report
On Error Resume Next

appWord.Documents.Add
With appWord.ActiveDocument
Selection.InsertAfter txtReportDetails
If Err.Number = 462 Then
CloseWordObject
MsgBox "Please run report again. If this happens more than" _
& vbCrLf & "once, please close out of Project before" _
& vbCrLf & "trying again.", , "Issues with running report"
End If

End With
Application.StatusBar = "Currently formatting report"
appWord.ScreenRefresh
appWord.Activate
FormatWord

appWord.ScreenRefresh
Application.StatusBar = ""

'Handle Errors Gracefully
Exit_EH:
Exit Sub

EH:
If Err.Number = 462 Then
CloseWordObject
MsgBox "Please run report again. If this happens more than" _
& vbCrLf & "once, please close out of Project before" _
& vbCrLf & "trying again.", , "Issues with running report"
Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_EH
End If

End Sub
Sub FormatWord()
Dim strReportTitle As String
Dim dtmReportDate As String
Dim appWordDoc As Word.Document

'Format Report Title
dtmReportDate = Now()
strReportTitle = "Who Does What Report" & vbCrLf & dtmReportDate
ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.InsertBefore
strReportTitle
ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.ParagraphFor
mat.Alignment = wdAlignParagraphCenter
'Set Tabs so the table will format nicely
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(0.5), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(1), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(4.5), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(5.5), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces

'Convert the text to a table
Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=5, _
AutoFitBehavior:=wdAutoFitContent
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With
Selection.Rows.AllowBreakAcrossPages = False
Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone

'Format the page breaks in the Word Document
With appWord.ActiveDocument
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "~~"
End With
While Selection.Find.Execute
Selection.SelectRow
Selection.Rows.Delete
Selection.InsertBreak Type:=wdPageBreak
Wend
Selection.TypeBackspace
Selection.Delete Unit:=wdCharacter, Count:=1

End With
'Format the Table
Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdCell
Selection.Rows.HeadingFormat = wdToggle
Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend
Selection.Cells.Merge
Selection.Font.Bold = wdToggle

Dim i As Integer
For i = 1 To ActiveDocument.Tables.Count
Selection.GoTo What:=wdGoToTable, Which:=wdGoToNext, Count:=1
Selection.MoveRight Unit:=wdCell
Selection.Rows.HeadingFormat = wdToggle
Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend
Selection.Cells.Merge
Selection.Font.Bold = wdToggle

Next i
Selection.Font.Bold = wdToggle
Selection.HomeKey Unit:=wdStory
Application.ActiveProject.Activate
'MsgBox "Report has finished.", , "Who Does What Report"
'appWord.Activate

End Sub
Private Sub CloseWordObject()
If TypeName(appWord) = "Application" Then
appWord.Quit SaveChanges:=wdDoNotSaveChanges
Set appWord = Nothing
End If

End Sub



--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.