Resource USage Sheets and VBA

[Jeff]

I am unsure as to how to select the resource usage sheet numbers/Data
in the grid to the right of the tasks along with the tasks. Any clues
or ideas? I have to create a report every week where I convert the
working hours to work man months(or a fraction thereof) and check it
against an excel spreadsheet dump of our timesheet application, then
manipulate it for forecasting. Once the data is in excel I'm fine, but
as you know, docuemtnatation and help on Project VBA exporting is
scarce.

 

[John]

Jeff,
You want to use the TimesScaleData Method. It can be configured to
extract Task, Resource or Assignment data in timescale fashion. I have
written several macros that do similar data manipulation. I suggest the
following pseudo-code:
1. Gather the basic data from Project using the TimeScaleData Method,
probably pjAssignmentTimescaledWork
2. Perform the necessary calculation on the data to convert the values
to man months
3. Open Excel using the GetObject or CreateObject Function
4. Export the calculated data into the Excel spreadsheet that has the
timesheet dump
5. Compare the data as needed
6. Perform the necessary data manipulation
7. Save or print the end result in a report

All of the above can be included in the macro, thus making the whole
operation a basic one step process (i.e. running the macro)

Believe it or not the various VBA help files have a wealth of
information for learning and using VBA. That's how I learned. You might
also want to take a look at a very informative tutorial on VBA. Go to
the MVP website at: http://www.mvps.org/project/links.htm
At the bottom of the page you will find a link for "Project 98 Visual
Basic Environment Training Materials". Although it says "Project 98", it
is equally applicable to all current versions of Project. And, if you
need further help with a macro, feel free to post to this newsgroup.

Hope this helps.
John
Project MVP

 

[Jeff]

I have tried what you've said, but the following code does not return
a value to my spreadsheet.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Sub TrasferTimescaleData()
Const intervalType As Integer = pjTimescaleWeeks
Const dataType As Integer = pjWork

'Link to Excel
Dim Xl As Excel.Application, XlSheet As Worksheet
Set Xl = CreateObject("Excel.Application")
Set XlSheet = Xl.Workbooks.Add.ActiveSheet

'Copy Left Table
'SelectRange 1, 2: SelectAll: EditCopy
'XlSheet.Paste XlSheet.Cells(2, 1)

'Transfer Table Headings
Dim i As Integer, Heading As String
For i = 1 To ActiveSelection.FieldNameList.Count
XlSheet.Cells(1, i) = ActiveSelection.FieldNameList(i)
Next

'Copy Time Scale Data
Dim TSValues As TimeScaleValues
Dim noColumns As Integer, startCol As Integer
ViewApply Name:="Resource &Usage"

'TimescaleEdit intervalType, intervalType
'DetailStylesRemoveAll
'DetailStylesAdd Item:=dataType
'Set TSValues =
ActiveProject.ProjectSummaryTask.TimeScaleData(ActiveProject.ProjectStart,
ActiveProject.ProjectFinish, , intervalType)
'noColumns = TSValues.Count
'PaneNext
SelectResourceColumn Column:="name"
OutlineHideSubTasks
'SelectColumn Column:=3
'ToggleAssignments
'SendKeys "^{HOME}"
TimescaleEdit MajorUnits:=2, MajorLabel:=9, Separator:=True,
MajorUseFY:=True, TierCount:=1
PaneNext
SelectTimescaleRange Row:=1, StartTime:="Fri 7/2/04", Width:=40,
Height:=1046917
'SendKeys "^{C}"
EditCopy
'EditCopy startCol
=XlSheet.Cells.SpecialCells(xlCellTypeLastCell).Column 1
'AppActivate ("Microsoft Excel")
'Xl.Workbooks.Add
'Application.ActivateMicrosoftApp (pjMicrosoftExcel)
'XlSheet.Paste XlSheet.Cells(2, startCol)



'Finally, Just a little formatting
XlSheet.Rows(1).Font.Bold = True
XlSheet.Range("A1",
XlSheet.Cells.SpecialCells(xlCellTypeLastCell).Address).EntireColumn.AutoFit
Xl.Visible = True
End Sub