none
Using ms project 2007 and vba macro to list all the custom fields used in the project? RRS feed

  • Question

  • Hi,Using ms project 2007 vba macro, I would like to be able to list all the custom fields used in the project and their corresponding field names. e.g. let us say I create a calculated duration field and name it "expected duration" and the name of the field I select is Duration1.

    I am trying to write a macro that will list all the used custom fields such as the result would look like:

    Duration1 ---> "expected duration"

    Text1       ---> "anything"

    Flag1        ---> "....."

    Number1  ---> "..............."

    Can anyone provide me with the solution?

    Regards,

    Chuck

    Monday, May 26, 2014 1:32 PM

Answers

  • Chuck,

    Couldn't help myself, here's the whole thing in condensed form. Just tweak the Case statement to delete any fields that aren't of interest.

    Again, if this answers your question, please mark this response as the answer.

    Sub getcustnames()
    Dim i As Integer, FldType As Integer, NumFlds As Integer
    Dim mytype As String, CusNam As String, usedfields As String
    'Note: fldconst defaults to type "variant"

    usedfields = "Custom Fields used in this file" & vbCrLf
    FldType = 1
    While FldType < 9
        Select Case FldType
            Case 1
                NumFlds = 30
                mytype = "text"
            Case 2
                NumFlds = 20
                mytype = "number"
            Case 3
                mytype = "flag"
            Case 4
                NumFlds = 10
                mytype = "duration"
            Case 5
                mytype = "cost"
            Case 6
                mytype = "date"
            Case 7
                mytype = "start"
            Case 8
                mytype = "finish"
        End Select
        usedfields = usedfields & vbCrLf & "--" & UCase(mytype) & "--" & vbCrLf
        For i = 1 To NumFlds
                fldconst = FieldNameToFieldConstant(mytype & i, pjTask)
                CusNam = CustomFieldGetName(fldconst)
                If CusNam <> "" Then
                    usedfields = usedfields & mytype & CStr(i) & " - " _
                        & CusNam & vbCr
                End If
        Next i
        FldType = FldType + 1
    Wend

    'MsgBox usedfields 'use this statement if you only have a few custom fields
    Debug.Print usedfields 'use this statement if you have a lot of custom fields

    End Sub

    John

    • Marked as answer by chamdan1 Tuesday, May 27, 2014 3:32 AM
    Tuesday, May 27, 2014 2:18 AM
  • Go it to work, here below is how:

    Replace:

    myType = "Text"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 30
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> "" Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i

    By:

    myType = "Text"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 30
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjTask)) <> "" Then
    '            nConst = ts(it).GetField(FieldNameToFieldConstant(myType & i, pjTask))' -- will display the content of Text1
                rNamedFld = CustomFieldGetName(FieldNameToFieldConstant(myType & CStr(i))) ' -- will display the renamed customed field
                usedfields = usedfields & myType & CStr(i) & " " & rNamedFld & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i

    Regards,

    Chuck


    • Marked as answer by chamdan1 Monday, May 26, 2014 6:47 PM
    • Edited by chamdan1 Monday, May 26, 2014 6:51 PM Forgot to put the image
    Monday, May 26, 2014 6:47 PM

All replies

  • chamdan1,

    As a quick approach, we know that there are 10 extra duration fields, 30 extra text fields, 20 extra flag fields, and 20 extra number fields, not counting enterprise fields. You could loop through each of those fields using their field constant value (e.g. Text1 > pjtasktext1) and with the FieldConstantToFieldName Method, you would get the custom field names, if there is one. If there is no custom name, the method will return a blank.

    There are also sets of resource and assignment extra fields. If you also want custom names for those, you will need extra loops. And just to keep it interesting, starting with Project 2007, there are actually separate task assignment and resource assignment extra fields.

    Note: there may be other ways to approach this using List objects, but I don't have time to detail that approach right now.

    John


    Monday, May 26, 2014 3:00 PM
  • Thanks John for your prompt reply, I will give it a try although I consider myself as a beginner in VBA but will try to build this macro and will get back to you if you don't mind should I fall into problem.

    Appreciate your help.

    Regards,Chuck

     
    Monday, May 26, 2014 3:34 PM
  • John,

    I found this module, which provides the the list of custom fields used in the project but does not provide the name given to the field. Here below is the module and hope you could help me achieve this by modifying the macro to list the renamed field.

    ' MSP Checks all Custom Task Fields
    Sub checkfields2()
    'This macro will check and report out which custom task fields are used
    'It requires Project 2002 and above as it relies on the GetField
    'and FieldNameToFieldConstant methods which were not introduced until
    '2002.
    'It does not include resource fields, however it is a simple matter to
    'do it by replacing the pjTask constant with pjResource.
    'Copyright Jack Dahlgren, Oct. 2004
    
    Dim mycheck As Boolean
    Dim myType, usedfields As String
    Dim t As Task
    Dim ts As Tasks
    Dim i, it As Integer
    
    Set ts = ActiveProject.Tasks
    usedfields = "Custom Fields used in this file" & vbCrLf
    
    myType = "Text"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 30
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> "" Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i
    
    myType = "Number"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 20
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> 0 Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i
    
    myType = "Duration"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 10
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If Left(ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)), 2) <> "0 " Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i
    myType = "Cost"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 10
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> 0 Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i
    
    myType = "Start"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 10
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> "NA" Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i
    
    myType = "Finish"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 10
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> "NA" Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i
    
    MsgBox usedfields
    
    End Sub
    This is what the module gives me. But I would like to have beside Text 1 the name that is shown as below. e.g Text1 is "Test".

    Would you mind helping me achieve this?

    Thanks in advance.

    Chuck

    Monday, May 26, 2014 4:25 PM
  • Go it to work, here below is how:

    Replace:

    myType = "Text"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 30
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjtask)) <> "" Then
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i

    By:

    myType = "Text"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 30
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjTask)) <> "" Then
    '            nConst = ts(it).GetField(FieldNameToFieldConstant(myType & i, pjTask))' -- will display the content of Text1
                rNamedFld = CustomFieldGetName(FieldNameToFieldConstant(myType & CStr(i))) ' -- will display the renamed customed field
                usedfields = usedfields & myType & CStr(i) & " " & rNamedFld & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i

    Regards,

    Chuck


    • Marked as answer by chamdan1 Monday, May 26, 2014 6:47 PM
    • Edited by chamdan1 Monday, May 26, 2014 6:51 PM Forgot to put the image
    Monday, May 26, 2014 6:47 PM
  • John,

    Although I have successfully realized what I was trying to do but once I use it fields other than Text1 .. Textn I get an error at the bold line???

    myType = "Number"
    usedfields = usedfields & vbCrLf & "--" & UCase(myType) & "--" & vbCrLf
    For i = 1 To 20
    mycheck = False
    it = 0
    While Not mycheck And (it < ts.Count)
        it = it + 1
        If Not ts(it) Is Nothing Then
            If ts(it).GetField(FieldNameToFieldConstant(myType & i, pjTask)) <> 0 Then
    '            nConst = ts(it).GetField(FieldNameToFieldConstant(myType & i, pjTask))' -- will display the content of Number1
                rNamedFld = CustomFieldGetName(FieldNameToFieldConstant(myType & CStr(i))) ' -- will display the renamed custom field
                usedfields = usedfields & myType & CStr(i) & vbCr
                mycheck = True
            End If
        End If
    Wend
    Next i

    " Run-time error mismatch "

    Is this related to the "FieldNameToFieldConstant" ?

    Regards,

    Chuck

    Monday, May 26, 2014 7:29 PM
  • Chuck,

    I didn't realize Jack had written a macro for that, however, it does seem a little bloated. There is no need to use the task object since custom fields are at project level. The following basic structure covers the custom text and number fields. I'll leave it as an exercise for the student to expand it for all custom fields.

    One more thought. If you have a lot of custom fields, the msgbox may exceed the screen height and you won't be able to see everything. I've added an option at the end of the macro to use the Immediate Window as an alternate output.

    Sub getcustnames()
    Dim i As Integer
    Dim mytype As String, CusNam As String, usedfields As String
    'Note: fldconst defaults to type "variant"

    usedfields = "Custom Fields used in this file" & vbCrLf
    mytype = "text"
    usedfields = usedfields & vbCrLf & "--" & UCase(mytype) & "--" & vbCrLf
    For i = 1 To 30
            fldconst = FieldNameToFieldConstant(mytype & i, pjTask)
            CusNam = CustomFieldGetName(fldconst)
            If CusNam <> "" Then
                usedfields = usedfields & mytype & CStr(i) & " - " _
                    & CusNam & vbCr
            End If
    Next i

    mytype = "number"
    usedfields = usedfields & vbCrLf & "--" & UCase(mytype) & "--" & vbCrLf
    For i = 1 To 20
            fldconst = FieldNameToFieldConstant(mytype & i, pjTask)
            CusNam = CustomFieldGetName(fldconst)
            If CusNam <> "" Then
                usedfields = usedfields & mytype & CStr(i) & " - " _
                    & CusNam & vbCr
            End If
    Next i
    MsgBox usedfields 'use this statement if you only have a few custom fields
    Debug.Print usedfields 'use this statement if you have a lot of custom fields

    End Sub

    If this answers your question, please mark my response as the answer.

    John





    Monday, May 26, 2014 9:04 PM
  • Chuck,

    Couldn't help myself, here's the whole thing in condensed form. Just tweak the Case statement to delete any fields that aren't of interest.

    Again, if this answers your question, please mark this response as the answer.

    Sub getcustnames()
    Dim i As Integer, FldType As Integer, NumFlds As Integer
    Dim mytype As String, CusNam As String, usedfields As String
    'Note: fldconst defaults to type "variant"

    usedfields = "Custom Fields used in this file" & vbCrLf
    FldType = 1
    While FldType < 9
        Select Case FldType
            Case 1
                NumFlds = 30
                mytype = "text"
            Case 2
                NumFlds = 20
                mytype = "number"
            Case 3
                mytype = "flag"
            Case 4
                NumFlds = 10
                mytype = "duration"
            Case 5
                mytype = "cost"
            Case 6
                mytype = "date"
            Case 7
                mytype = "start"
            Case 8
                mytype = "finish"
        End Select
        usedfields = usedfields & vbCrLf & "--" & UCase(mytype) & "--" & vbCrLf
        For i = 1 To NumFlds
                fldconst = FieldNameToFieldConstant(mytype & i, pjTask)
                CusNam = CustomFieldGetName(fldconst)
                If CusNam <> "" Then
                    usedfields = usedfields & mytype & CStr(i) & " - " _
                        & CusNam & vbCr
                End If
        Next i
        FldType = FldType + 1
    Wend

    'MsgBox usedfields 'use this statement if you only have a few custom fields
    Debug.Print usedfields 'use this statement if you have a lot of custom fields

    End Sub

    John

    • Marked as answer by chamdan1 Tuesday, May 27, 2014 3:32 AM
    Tuesday, May 27, 2014 2:18 AM
  • John,

    I really appreciate your help. Two Thumb Up! Wow! pretty fast process compared to the one Jack had and the results came up perfect!

    Cheers! You really deserve a cool beer!

    Regards,

    Chuck

    Tuesday, May 27, 2014 3:32 AM
  • Chuck,

    John has already provided the answer, but just in case pls also see the small routine for getting a list of all custom field formulas, here at: http://www.msptips.com/#Tip7

    Regards -- Ismet


    Tuesday, May 27, 2014 4:12 AM
  • Chuck,

    You're welcome and thanks for the feedback.

    John

    Tuesday, May 27, 2014 1:04 PM