Use VBA to determine visible field columns in Project and send data to Excel

Question

I have a conundrum.  I know the proper syntax to send data from a particular field to Excel, if I know in advance that I will be using that field.  For example if I wanted to send information from the "Text 1" field I could use something along these lines:

TotalSelection=ActiveSelection.Tasks.Count
for i=1 to TotalSelection
 xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).Text1
Next i

What I want to know is, how would I go about transferring data to Excel through VBA when I don't know the fields in advance?  If I want to be able to export whatever project fields a particular project has open, and only those fields, and I don't know what those fields are in advance (as they could change from project file to project file) how could I code that?  How can I make the Task.Property variable?  (or is that even possible?)  I found some old that determines what fields are already visible.  See below:

 

Sub FindFieldNames()
    Dim TB As Table
    Dim CTR As Integer
    For Each TB In ActiveProject.TaskTables
        If TB.Name = ActiveProject.CurrentTable Then
            Exit For
        End If
    Next
    For CTR = 1 To TB.TableFields.Count
        If CTR = 1 Then
            FieldName = FieldConstantToFieldName(TB.TableFields(CTR).Field)
        Else
            FieldName = FieldName & Chr(13) & FieldConstantToFieldName(TB.TableFields(CTR).Field)
        End If
    Next CTR
   
    MsgBox FieldName
End Sub

FieldName obviously is a string (that increments each time through), listing all the field columns that were visible.  Can I use this approach to find the field name (as a string) and somehow use that to specifiy the property I want to invoke for a task in the ActiveSelection? 

TotalSelection=ActiveSelection.Tasks.Count
for i=1 to TotalSelection
 xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).FieldConstantToFieldName(TB.TableFields(CTR).Field)  <--x know this is not valid but can something like this be done? 
Next i

If this is not possible, is there a way to reference the field column by index or fieldconstant? 

Answer 1

Hi B Fennell,

To retreive the content of a task field in VBA, you can use the 'GetField' method.
The GetField method requires a field constant as its parameter. So you even don't need to use the FieldConstantToFieldName constant.

So you could achieve what you want by using something like:

xlRow.Offset(i, 0) = ActiveSelection.Tasks(i).GetField(TB.TableFields(CTR).Field)

Some other remark about your code:

'TotalSelection=ActiveSelection.Tasks.Count' will fail if the active view is not a task view. So you might want to check for that.

An empty task row will make your code fail. You could this using:

Dim TSK as Task

For each TSK in ActiveSelection.Tasks
If Not TSK Is Nothing Then
xlRow.Offset(i, 0) = TSK.GetField(TB.TableFields(CTR).Field)
End If
Next

I hope this helps,
Hans

---

My EPM blog: Projectopolis

Answer 2

here is some code that should do what you want. you will just need to 'attach' excel.

 

Sub FindFieldNames()
Dim TB As Table
Dim Counter As Integer
Dim FieldCount As Integer
Dim FieldNames() As String
Dim T As Task


For Each TB In ActiveProject.TaskTables
  If TB.Name = ActiveProject.CurrentTable Then
    Exit For
  End If
Next TB

'2010 has an "Add new column" field a the end that throws off this code so im
'checking to see if this is version 14 or not. If it is then I reduce the Fieldcount by 1

If Application.Version = "14.0" Then
  FieldCount = TB.TableFields.Count - 1
Else
  FieldCount = TB.TableFields.Count
End If
'________________________________________________________________________________________

'Redim the Array

ReDim FieldNames(1, FieldCount) As String
'_________________________________________

'Get the names of the fields and put them into the array
For Counter = 1 To FieldCount
  FieldNames(1, Counter) = FieldConstantToFieldName(TB.TableFields(Counter).Field)
Next Counter
'_______________________________________________________

'Loop through tasks and get the data for the visible fields and do something with it.
For Each T In ActiveProject.Tasks
  If Not (T Is Nothing) Then
    For Counter = 1 To FieldCount
    'I did not have excel 'attached' to my code so I just tested with a debug.print.
'      xlRow.Offset(ActiveProject.Tasks.Count, 0) = T.GetField(Application.FieldNameToFieldConstant(FieldNames(1, Counter)))
      Debug.Print T.GetField(Application.FieldNameToFieldConstant(FieldNames(1, Counter)))
    Next Counter
  End If
Next T

End Sub

---

Brian Kennemer – DeltaBahn Senior Architect
Blog | Twitter | LinkedIn

Answer 3

Hans and Brian

A thousand thank yous to you both for that "GetField" method.  That did the trick.  Also thanks for the head's up on the "If Not Task" contingency.  I hadn't run into that but I was bound to at some point, so it's like I got 2 solutions for the price of one.  Thank you both once again.