I received more timely answers from the Microsoft forum here (remove all dashes):
h-t-t-p:-/-/-t-i-n-y-u-r-l-.-c-o-m-/-k-q-2-e-t-x
They helped me arrive at the right solution. I'm posting the code in case someone else wants to use it:
Dim xlApp As Excel.Application
Dim xlRange As Excel.Range
Dim Dept As Task
Dim Check As String
Sub ExportMasterScheduleData()
'Start Excel and create a new workbook
'Create column titles
'Export data and the project title
'Tidy up
'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") = "Project"
xlRange.Range("B2") = "Dependency"
xlRange.Range("C2") = "Notes"
xlRange.Range("D2") = "Need Date Last Updated"
xlRange.Range("E2") = "Need Date"
xlRange.Range("F2") = "Deliverable ECD"
xlRange.Range("G2") = "Status"
xlRange.Range("H2") = "Deliverable Owner"
With xlRange.Range("A2:N2")
.Font.Bold = True
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
.EntireColumn.AutoFit
.Select
End With
'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