none
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? 

    Thursday, July 21, 2011 5:52 PM

Answers

  • 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
    • Marked as answer by B Fennell Friday, July 22, 2011 4:56 PM
    Thursday, July 21, 2011 7:35 PM
    Moderator
  • 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
    • Marked as answer by B Fennell Friday, July 22, 2011 4:58 PM
    Thursday, July 21, 2011 8:07 PM
    Moderator

All replies

  • 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
    • Marked as answer by B Fennell Friday, July 22, 2011 4:56 PM
    Thursday, July 21, 2011 7:35 PM
    Moderator
  • 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
    • Marked as answer by B Fennell Friday, July 22, 2011 4:58 PM
    Thursday, July 21, 2011 8:07 PM
    Moderator
  • 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.

    Friday, July 22, 2011 4:55 PM