Excel VBA - create column names using MS Project headers

Question

I'm in the middle of writing a script that populates an excel spreadsheet with data from an MS Project file. I would like the script to recognize the title name of the MS Project columns as I have a number of custom columns with different names (custom number fields are populated with different names)

The code below was my attempt, but i'm getting an error when it comes to writing the value of the task column title to the sheet, am I doing something wrong here?

Sub PopulateSheet()
Dim Proj             As MSProject.Application
Dim NewProj          As MSProject.Project
Dim t                As MSProject.Task        

Dim xl as workbook
Dim s as worksheet
Dim Newsheet as worksheet

Set Xl = ThisWorkbook
BookNam = Xl.Name
Set Newsheet = Xl.Worksheets.Add

'Code to find and open project files
Set Proj = New MSProject.Application
MsgBox ("Please Select MS Project File for Quality Checking")

'Select Project File
FileOpenType = Application.GetOpenFilename( _
               FileFilter:="MS Project Files (*.mpp), *.mpp", _
               Title:="Select MS Project file", _
               MultiSelect:=False)

'Detect if File is selected, if not then stop code
If FileOpenType = False Then
    MsgBox ("You Havent Selected a File")
    Exit Sub
End If

'Write the FileOpenType variant to two separate strings
NewProjFilePath = Left$(FileOpenType, InStrRev(FileOpenType, "\"))
NewProjFileName = Mid$(FileOpenType, InStrRev(FileOpenType, "\") + 1)       

Newsheet.Name = NewProjFileName
Set s = Newsheet

'Populate spreadsheet header row with column titles from MS Project
s.Range("A1").Value = t.Number1  ***<-- Error '91' - Object variable or With block variable not set***

End Sub

Answer 1

Here is generic code that loops through the fields in the active task table and prints out the field headings as displayed in the table.

Sub GetTaskTableHeaders()

    Dim t As Table
    Set t = ActiveProject.TaskTables(ActiveProject.CurrentTable)
    Dim f As TableField
    For Each f In t.TableFields
        If f.Field > 0 Then
            Dim header As String
            Dim custom As String
            custom = Application.CustomFieldGetName(f.Field)
            If Len(f.Title) > 0 Then
                header = f.Title
            ElseIf Len(custom) > 0 Then
                header = custom
            Else
                header = Application.FieldConstantToFieldName(f.Field)
            End If
            Debug.Print "Field " & f.Index, header
        End If
    Next f

End Sub

Note that fields can be customized at the project level to be given a different title, or they can be customized at the table level. This code looks for both customizations and if neither is found, the field name is used.