Hi ladivapr:
The following macro script was taken from Woodie's OfficeWatch newsletter
(it's a great FREE reference, BTW):
===========beginning of article============
Excel / Access Integration
Preparing the Project Files for the Report
When reporting across multiple projects, you need to use common naming
conventions across all the projects, and all the support information must be
stored in the same place in each file. For example, resource names must be
spelled the same way in each file, and the project title should be stored in
the same place.
The best place for project titles is the Title field in the File,
Properties dialog box. You can also populate that field by entering text in
the Name column when you display the project summary task (select Tools,
Options and in the View tab select the Show Project Summary Task option). If
your project title is fairly long, you will get a very wide column in the
Excel report. This macro allows you to enter a short title for use in the
report by entering the short title in the Subject field of Project's File,
Properties dialog box. If the macro finds text in the Subject field it will
use that text for the title instead of using the entry in the Title field.
Note: if you're already using the Subject field you can substitute one
of the other text fields found on the Properties Summary tab in the macro.
For this macro to run properly, make sure that resource names are
spelt the same way in each file, that there is a project title and if
wanted, a short title in the Subject field.
See the Tip Using Workspace Files in this issue for a timesaver when
working repeatedly with the same group of project files.
The Macro's Structure
This macro has a simple four step structure:
1.. Prepare the Excel worksheet, creating the title for the report
and the column titles
2.. Export summary data for the resource assignments
3.. Export timephased data for the resource assignments
4.. Add any Excel formulae that may be needed in the report, sort
the data, and tidy up by safely closing connections to Excel etc
This month we have the code for parts 1 and 2. Next month we will
export the timephased data and look at some variations on this macro.
Setting up Project to Create Excel Worksheets
The macro will be in Project VBA, but it will be talking to Excel VBA.
To do this a reference needs to be setup so Project VBA knows Excel VBA's
objects, methods and properties.
To create a blank macro module and the reference to Excel:
1.. In Project press Alt+F11 to open the Visual Basic Editor (VBE).
2.. In the VBE select Insert, Module.
3.. Select Tools, References and scroll down the list of references
to Microsoft Excel 8.0 Object Library for Excel 97, 9.0 for Excel 2000 or
10.0 for Excel 2002.
4.. Click OK.
The Macro
The macro that follows can either be copied from here to your module
or downloaded from
www.projectlearning.com/wpw/ <
http://www.projectlearning.com/wpw/>
If you do download the project file make sure you check the Excel
reference (step 3 above) before running the code. The downloaded code has
some extra comments explaining what the code does.
Sub WhoDoesWhatWhen()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlR As Excel.Range
Dim Proj As Project
Dim R As Resource
Dim A As Assignment
Dim ProjectName As String
Dim i As Integer
On Error Resume Next 'Point to Excel Application
Set xlApp = GetObject(, "Excel.Application") 'Look for running copy
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application") 'Start new instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
End If
Application.ActivateMicrosoftApp pjMicrosoftExcel
Set xlBook = xlApp.Workbooks.Add
Set xlR = xlBook.Worksheets(1).Range("A1")
'Create report header
With xlR
..Formula = "Who Does What When"
..Font.Bold = True
..Font.Size = 14
End With
With xlR.Range("A2")
..Formula = "As of " & Format$(Date, "Long Date")
..Font.Bold = True
..Font.Italic = True
..Font.Size = 12
End With
Set xlR = xlR.Range("A4") 'Add headers and months for year
xlR.Range("A1:E1") = Array("Resource Name", "Task Name", "Project
Name", "Total Work", "Total Cost")
With xlR.EntireRow
..HorizontalAlignment = xlHAlignCenter
..WrapText = True
..Font.Bold = True
End With
xlR.Range("A1").ColumnWidth = 20
xlR.Range("B1:C1").ColumnWidth = 30
xlR.Range("D1").EntireColumn.NumberFormat = "#,##0\h"
xlR.Range("E1").EntireColumn.NumberFormat = "$#,##0"
For i = 1 To 12
xlR.Offset(0, i + 4) = DateSerial(Year(Date), Month(Date) + i - 1, 1)
Next
xlR.Range("F1:Q1").NumberFormat = "mmm yy" 'Copy Data
Set xlR = xlR.Offset(1, 0)
For Each Proj In Projects
If Proj.BuiltinDocumentProperties("Subject") = "" Then
ProjectName = Proj.BuiltinDocumentProperties("Subject")
Else
ProjectName = Proj.BuiltinDocumentProperties("Title")
End If
For Each R In Proj.Resources
For Each A In R.Assignments
xlR.Range("A1:E1") = Array(A.ResourceName, A.TaskName, ProjectName, A.Work /
60, A.Cost)
Set xlR = xlR.Offset(1, 0)
Next
Next
Next
End Sub
The first section on creating the connection to Excel has been covered
in previous articles (see WPW 2-12 and the Resource Calendars macro).
However, if it isn't clear what
the code is doing, try single stepping through the code using the F8
function key. Click on each command and press F1 for help to learn more.
The next section uses With statements to do more than one action on
the same cell range. A With statement creates more efficient and faster
running code, so it's particularly efficient within a loop. Again this is
all code used before (see WPW 2-07).
One new bit of code might be the Format$ method. It is using a format
of Long Date. There is also a Short Date and Medium Date format available.
Their power is that they use the format setup in the Control Panel for
Regional Options. This means that updating regional options updates the date
formats for all cells in your spreadsheet automatically and it also handles
international date conflicts (most countries outside the US and Canada use
dd mmm rather than mmm dd date formats).
The Format$ statement that formats the Cost column can be changed to
the format "$#," to display the cost in thousands of dollars. If you choose
to do this be sure to edit the column title to include (thousands) to avoid
confusion.
A part of the code that can be confusing is that which uses xlR.Range
to place the column headers in the worksheet. Since the report title has
already been placed in cell A1, it would appear that the code overwrites it
with the column header Resource Name. If you have a statement
Range("A1:E1"), without the xlR. in front, then the range "A1:E1" refers to
the cells A1:E1 in the spreadsheet. With xlR. in front, however,
Range("A1:E1") is relative to xlR. Since the line Set xlR = xlR.Range("A4")
set xlR to point to A4, the line xlR.Range("A1:E1") actually refers to cells
"A4:E4". To prove this, step through your code using the F8 function key.
The title cells do end up in cells A4 to E4.
xlR.Range("A1:E1") = Array( lets you add a number of values into the
same number of cells in one statement. Without using an array you need one
statement per cell.
For i = 1 To 12
xlR.Offset(0, i + 4) = DateSerial(Year(Date), Month(Date) + i - 1, 1)
Next
This code adds the date of the first day of the current month onwards
for a full year. The next statement formats the cells in Excel to mmm yy
format.
The last section loops through each project and if there is a short
title in the Subject field it uses it. If not it uses the Title field which
is the same as the task name for the project summary task. Within that loop
the codes loops through all resources then all assignments for each
resource. The For Each construct is the simplest and most effective way of
looping through any collection of objects.
Another Array statement is used to add all data for the assignment and
the xlR object is set to the next row down. Note that the assignment's work
value is divided by 60 since work is stored as a number of minutes.
=========end of article==========
Hope this helps...
Regards,
Al
