See the code  below. Note, the code is run  from MS Project.

----------------------------------------------------------------
Sub HeatMapExtractProjectToExcel()

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim xlRow As Excel.Range

Dim xlCol As Excel.Range

Dim Proj As Project

Dim t As Task

Dim ColumnCount, Columns, Tcount As Integer

Tcount = 0
ColumnCount = 0

Set xlApp = New Excel.Application '<<<<<this is where the compile
error occurs

xlApp.Visible = True

AppActivate "Microsoft Excel"

xlApp.Cursor = xlWait

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets.Add

xlSheet.Name = ActiveProject.Name

' set range to write to first cell

Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
xlRow = xlRow.Offset(1, 0)
xlRow = Date
Set xlRow = xlRow.Offset(2, 0)

Set xlCol = xlRow.Offset(0, 0)
xlCol = "ENV ID" ' Env ID

Set xlCol = xlRow.Offset(0, 1)
xlCol = "ENV TYPE" ' Env Type

Set xlCol = xlRow.Offset(0, 2)
xlCol = "WORK TYPE" ' Work Type

Set xlCol = xlRow.Offset(0, 3)
xlCol = "REL START WK" ' Rel Start Week

Set xlCol = xlRow.Offset(0, 4)
xlCol = "DURATIONWKS" ' Duration in weeks

Set xlRow = xlRow.Offset(2, 0)

'Write each task and indent to match outline level

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If Not t.Summary Then
Set xlRow = xlRow.Offset(1, 0)

Set xlCol = xlRow.Offset(0, 0)
xlCol = t.Text23 ' Env ID

Set xlCol = xlRow.Offset(0, 1)
xlCol = t.Text24 ' Env Type

Set xlCol = xlRow.Offset(0, 2)
xlCol = t.Text27 ' Work Type

Set xlCol = xlRow.Offset(0, 3)
xlCol = t.Text25 ' Rel Start Week

Set xlCol = xlRow.Offset(0, 4)
xlCol = (t.Duration1) / (60 * 8 * 5) ' Convert Duration
from minutes to weeks

Tcount = Tcount + 1
End If

End If
Next t

'switch back to project  and display completion message

AppActivate "Microsoft Project"
MsgBox ("Macro Complete with " & Tcount & " Non-Summary Tasks
Written")
AppActivate "Microsoft Excel"
xlApp.Cursor = xlDefault

End Sub