Need help with writing a Macro

[Raj]

I want to write a macro which generates a excel sheet that shows a report for tasks that should have finished yesterday, today and the tasks scheduled to be complete in the next 3 days (basically a 5 day window report)

How can I do this? Any help would be appreciated.

My email (e-mail address removed)

 

[John]

Raj,
Is there a reason you want to put the data in Excel? An interactive
filter can be created to capture the 5 day window and the resulting view
can be printed directly, or if desired, the filter can be incorporated
into a custom report which can then be printed.

However if you really want/need to get the data into Excel, there are
probably numerous ways to transfer the data. I would probably loop
through all tasks in the file looking for (schedule) Finish dates in the
5 day window. As they are found, I would load the revelant data (i.e.
Name, Start, Finish, etc.) into a matrix. Then I would open Excel and
export the contents of the matrix. Finally I would format the Excel
spreadsheet. The basic structure would look something like this:
(Disclaimer - I believe the basic structure of this code is sound but I
did not test it).

Sub FiveDay()
Dim TNam() as String
Dim [other matricies]

'Gather schedule finish data from Project into matricies
MaxTsk = ActiveProject.Tasks.Count
ReDim TNam(MaxTsk)
ReDim [other matricies]
MinDate = Application.DateSubtract(Today,"1d")
MaxDate = Application.DateAdd(Today,"3d")
i = 0
For Each t in ActiveProject.Tasks
If Not t is Nothing Then
If t.Summary = False Then
If t.Finish >= MinDate And t.Finish <= MaxDate Then
i = i + 1
TNam(i) = t.Name
[use other matricies for more task data]
End If
End If
End If
Next t

'Open Excel if not already running and dump matricies
Set XL = GetObject(, "Excel.application")
If Err <> 0 Then
On Error GoTo 0
Set XL = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Excel application is not available on this workstation" _
& Chr(13) & "Install Excel or check network connection",
vbCritical
FilterApply Name:="all tasks"
ViewApply Name:=oldview
Set XL = Nothing
On Error GoTo 0 'clear error function
Exit Sub
End If
End If
XL.Workbooks.Add
Set c = XL.Workbooks(XL.ActiveWorkbook.Name).worksheets(1).Range("A1")
RowIndx = 0
For j = 1 to i
c.offset(RowIndx,0).value = TNam(j)
[similar syntax for other matrix values]
RowIndx = RowIndx + 1
Next j

[format the spreadsheet as needed]
Set XL = Nothing
End Sub

John

 

[Gérard Ducouret]

Hello Raj,

First of all, you have to create a filter which will do that selection :
"Filtrer 2 d before 3 days after" :
Project / Filter for / More filters...
Finish ...Between... "Begining of periode"?;"End of periode"?
NB : I don't have the english version in front of me !
Then you write the VBA procedure which will activate this filter :

Sub Finish5days()
Dim RightNow As Date
Dim TwoDaysBefore As Date, ThreeDaysAfter As Date

RightNow = Date 'Read the date of the internal clock of your PC
TwoDaysBefore = RightNow - 2
ThreeDaysAfter = RightNow + 3

FilterApply Name:="Filtrer 2 d before 3 days after", value1:=TwoDaysBefore,
value2:=ThreeDaysAfter

End Sub

Gérard Ducouret

I want to write a macro which generates a excel sheet that shows a report

for tasks that should have finished yesterday, today and the tasks scheduled
to be complete in the next 3 days (basically a 5 day window report)

 

[Gérard Ducouret]

The continuation...
I forgot the code which exports data toward Excel via a mapp, but it is easy
to write it by auto-recording...
Hope this help,

Gérard Ducouret