Selection of Tasks using VBA

[Chris]

I have this code tweaked from Rod Gill almost perfect. It is exporting tasks
to an Excel spreadsheet, Unfortunately, I am trying to get it to filter on
the custom field, Text11. If it has a value of "External" then export data
from that task. Otherwise, ignore it and find the next task with that field
set to "External." I've tried every combination for the last 2 hours now and
I just can't get it to filter. Help!
Sub ExportMasterScheduleData()
'Start Excel and create a new workbook
'Create column titles
'Export data and the project title
'Tidy up
Dim xlApp As Excel.Application
Dim xlRange As Excel.Range
Dim Dept As Task
Dim Check As String

'Start Excel and create a new workbook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add

'Create column titles
Set xlRange = xlApp.Range("A1")
With xlRange
.Formula = "Master Schedule Report"
.Font.Bold = True
.Font.Size = 12
.Select
End With

xlRange.Range("A2") = "WBS"
xlRange.Range("B2") = "Project Name"
xlRange.Range("C2") = "Owner"
xlRange.Range("D2") = "Start"
xlRange.Range("E2") = "End"
xlRange.Range("F2") = "Dependencies"
xlRange.Range("G2") = "Dependency Owner"
xlRange.Range("H2") = "Need Date"
xlRange.Range("I2") = "Last Update"
xlRange.Range("J2") = "Deliverables"
xlRange.Range("K2") = "Deliverable Owners"
xlRange.Range("L2") = "Ready Date"""
xlRange.Range("M2") = "ECD"
xlRange.Range("N2") = "Notes"
With xlRange.Range("A2:N2")
.Font.Bold = True
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With

'Export data and the project title
Set xlRange = xlRange.Range("A3")
For Each Dept In ActiveProject.Tasks
Check = Dept.Text11
If Not Check = "0" Then
With xlRange
.Range("A3") = Dept.WBS
.Range("B3") = ActiveProject.Name
.Range("C3") = Dept.ResourceNames
.Range("D3") = Dept.Start
.Range("E3") = Dept.Finish
.Range("F3") = Check
.Range("G3") = Dept.Text10
.Range("H3") = Dept.Finish
.Range("O3") = Dept.Text11
End With
Else: End If
Set xlRange = xlRange.Offset(1, 0) 'Point to next row
Check = ""
Next Dept
'Tidy up
xlRange.Range("A3:H3").EntireColumn.AutoFit
Set xlApp = Nothing
End Sub

 

[Jan De Messemaeker]

Hi,

Replace

If Not Check = "0" Then

by
if Check="External" then

HTH
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Chris]

Thanks for posting back! Unfortunately, that did not work and now nothing is
being printed out. The spaces didn't seem to matter. As soon as I saved the
macro, VBA put the spaces back in like this:
If Check = "External" Then

 

[Jan De Messemaeker]

Sorry. is it "External" or "External. " as you wrote?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Chris]

Just "External" with no period. Sorry for the confusion

 

[Jan De Messemaeker]

You could try this

If instr(lcase(check),"external")>0 then

that would take care of leading or trailing blanks

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Chris]

Thanks for the tips. I'm sorry that didn't work either. I'm getting tired
of working on this. It's been all day trying to do a simple check of a
property in and If Then statement. I give up. I'll just auto filter it once
it gets to excel. Thanks for your help.

 

[John]

Thanks for the tips. I'm sorry that didn't work either. I'm getting tired
of working on this. It's been all day trying to do a simple check of a
property in and If Then statement. I give up. I'll just auto filter it once
it gets to excel. Thanks for your help.

Chris,
Sorry I'm late in joining this discussion but I think a much more
efficient approach would be to create a filter using the FilterEdit
Method in Project. That will automatically find all tasks with Text11 =
"External". Then use the following syntax to loop through just the
filtered tasks.
SelectTaskColumn
For Each Dept in ActiveSelection.Tasks

I use this approach in many of my macros.

Note, since this approach works using foreground processing, you might
want to save the current view, transfer data to Excel, and then restore
the old view.

Hope this helps.

John
Project MVP

 

[MichaelA]

Hello,

I use exactly the approach defined below to export various tasks to
external applications and works perfect. Set the filter, change the
view, extract, change the view back and all done.


Regards
Michael A.

 

[Chris]

Thank you. I am still learning the Project Object Model, so it will take
some work for me to find view objects, but the logic sounds good. I'll give
that a shot and report back on what happens.

 

[Chris]

It all works! Thanks for everyone's help here. Here's the final code I
used, in case someone else wants to start with it:

'Export data and the project title
Set xlRange = xlRange.Range("A3") 'Set cursor to the right spot in the
worksheet
ViewApply Name:="Chris Baseline Gantt View" 'Get the view that has the
Text11 column to filter on
OutlineShowAllTasks 'Any hidden tasks won't be selected, so be sure all
tasks are showing
FilterApply Name:="External Tasks" 'This custom filter selects "External"
SelectTaskColumn ("Text11") 'Insures the For Each loop gets all of the
filtered tasks, this may be redundant
For Each Dept In ActiveSelection.Tasks 'Pulls data for each task into
spreadsheet
With xlRange
.Range("A3") = ActiveProject.Name
.Range("B3") = Dept.Name
.Range("C3") = Dept.Notes
.Range("E3") = Dept.Finish
.Range("H3") = Dept.Text10
End With
Set xlRange = xlRange.Offset(1, 0) 'Point to next row
Next Dept
'Tidy up
ViewApply Name:="Chris Baseline Gantt View"
FilterApply Name:="External Tasks"
With xlRange
.Range("A3").ColumnWidth = 30
.Range("B3").ColumnWidth = 50
.Range("B3").EntireColumn.WrapText = True
.Range("C3").ColumnWidth = 30
.Range("E3").ColumnWidth = 20
.Range("C3").EntireColumn.WrapText = True
End With
Set xlApp = Nothing
End Sub

 

[John]

Thank you. I am still learning the Project Object Model, so it will take
some work for me to find view objects, but the logic sounds good. I'll give
that a shot and report back on what happens.

Chris,
You're welcome. I thought you'd perhaps given up based on your last
thread post but I'm pleased to see you are still working on it.

John
Project MVP

 

[John]

It all works! Thanks for everyone's help here. Here's the final code I
used, in case someone else wants to start with it:

'Export data and the project title
Set xlRange = xlRange.Range("A3") 'Set cursor to the right spot in the
worksheet
ViewApply Name:="Chris Baseline Gantt View" 'Get the view that has the
Text11 column to filter on
OutlineShowAllTasks 'Any hidden tasks won't be selected, so be sure all
tasks are showing
FilterApply Name:="External Tasks" 'This custom filter selects "External"
SelectTaskColumn ("Text11") 'Insures the For Each loop gets all of the
filtered tasks, this may be redundant
For Each Dept In ActiveSelection.Tasks 'Pulls data for each task into
spreadsheet
With xlRange
.Range("A3") = ActiveProject.Name
.Range("B3") = Dept.Name
.Range("C3") = Dept.Notes
.Range("E3") = Dept.Finish
.Range("H3") = Dept.Text10
End With
Set xlRange = xlRange.Offset(1, 0) 'Point to next row
Next Dept
'Tidy up
ViewApply Name:="Chris Baseline Gantt View"
FilterApply Name:="External Tasks"
With xlRange
.Range("A3").ColumnWidth = 30
.Range("B3").ColumnWidth = 50
.Range("B3").EntireColumn.WrapText = True
.Range("C3").ColumnWidth = 30
.Range("E3").ColumnWidth = 20
.Range("C3").EntireColumn.WrapText = True
End With
Set xlApp = Nothing
End Sub

Chris,
It looks like you've got it. Just for reference, the SelectTaskColumn
does not need to specifically include the column field name. By default
the first column will be selected if no field is specified but you don't
care because all you need to do is to set the selection of tasks to be
the whole group that was filtered. And no, the line is not redundant. If
you don't select the task column, the "For Each" loop will go for
exactly one task, the first, which is the only active selection.

By the way, you don't need to manually set up the custom filter. You can
do that in code using the FIlterEdit Method.

John
Project MVP