none
Export Project Tasks to Excel using VBA by Custom Field RRS feed

  • Question

  • I want to export tasks to Excel in a Custom Field with specific text. Only export tasks which have "Yes" in a selected Custom Field (Text20 for example) to Excel.

    Below is what I have so far, but It leaves empty rows in spreadsheet where tasks do not match the criteria. I need a contiguous list of tasks meeting the "Yes" criteria. I'm thinking the script is formatted incorrectly. Need Help refining this query.

    I would also like it to create a spreadsheet instead of opening an existing spreadsheet.

    Sub ExportToExcel()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim proj As Project
    Dim t As Task
    Dim pj As Project
    Set pj = ActiveProject
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    AppActivate "Microsoft Excel"
    Set xlBook = xlApp.Workbooks.Open("C:\Templates\exporttest.xlsm")
    Set xlSheet = xlBook.Worksheets(1)
    xlSheet.Cells(1, 1).Value = "Project Name"
    xlSheet.Cells(1, 2).Value = pj.Name
    xlSheet.Cells(2, 1).Value = "Project Title"
    xlSheet.Cells(2, 2).Value = pj.Title
    xlSheet.Cells(4, 1).Value = "Task ID"
    xlSheet.Cells(4, 2).Value = "Task Name"
    xlSheet.Cells(4, 3).Value = "Task Start"
    xlSheet.Cells(4, 4).Value = "Task Finish"
    xlSheet.Cells(4, 5).Value = "Task Notes"
    xlSheet.Cells(4, 6).Value = "Summary Task"
    xlSheet.Cells(4, 7).Value = "Predecessor Tasks"
    For Each t In pj.Tasks
    If t.Text20 = "Yes" Then
        xlSheet.Cells(t.ID + 4, 1).Value = t.ID
        xlSheet.Cells(t.ID + 4, 2).Value = t.Name
        xlSheet.Cells(t.ID + 4, 3).Value = t.Start
        xlSheet.Cells(t.ID + 4, 4).Value = t.Finish
        xlSheet.Cells(t.ID + 4, 5).Value = t.Notes
        xlSheet.Cells(t.ID + 4, 6).Value = t.Summary
        xlSheet.Cells(t.ID + 4, 7).Value = t.Predecessors
    End If
    Next t
    End Sub

    Tuesday, February 4, 2014 8:32 PM

Answers

  • Nearly there, just two things you needed to amend:

    1 - Rather than "Workbooks.Open", use "Workbooks.Add" - this will create a document based on the template, rather than opening the actual template

    2 - Use an integer variable (i) to count the number of tasks that meet your criteria, to determine which row in the spreadsheet you should be using

    See the example below.

    Some people would say this is over-complicated, as you could use a filter to get the tasks that meet your criteria, then a map to do the same export by going into File > Save As, and select "Excel Workbook (*.xlsx)" format. This will allow you to set up a new map which will allow you to select the fields you want in there.

    However, I can understand the need to do it this way if you've got a special template you want to slot the data into (I personally do this a lot).

    Sub ExportToExcel()
    
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim proj As Project
    Dim t As Task
    Dim pj As Project
    Dim i As Integer
    
    Set pj = ActiveProject
    Set xlApp = New Excel.Application
    
    xlApp.Visible = True
    AppActivate "Microsoft Excel"
    
    Set xlBook = xlApp.Workbooks.Add(Template:="C:\Templates\exporttest.xlsm")
    Set xlSheet = xlBook.Worksheets(1)
    
    xlSheet.Cells(1, 1).Value = "Project Name"
    xlSheet.Cells(1, 2).Value = pj.Name
    xlSheet.Cells(2, 1).Value = "Project Title"
    xlSheet.Cells(2, 2).Value = pj.Title
    xlSheet.Cells(4, 1).Value = "Task ID"
    xlSheet.Cells(4, 2).Value = "Task Name"
    xlSheet.Cells(4, 3).Value = "Task Start"
    xlSheet.Cells(4, 4).Value = "Task Finish"
    xlSheet.Cells(4, 5).Value = "Task Notes"
    xlSheet.Cells(4, 6).Value = "Summary Task"
    xlSheet.Cells(4, 7).Value = "Predecessor Tasks"
    
    i = 4
    
    For Each t In pj.Tasks
        If t.Text20 = "Yes" Then
            i = i + 1
            xlSheet.Cells(i, 1).Value = t.ID
            xlSheet.Cells(i, 2).Value = t.Name
            xlSheet.Cells(i, 3).Value = t.Start
            xlSheet.Cells(i, 4).Value = t.Finish
            xlSheet.Cells(i, 5).Value = t.Notes
            xlSheet.Cells(i, 6).Value = t.Summary
            xlSheet.Cells(i, 7).Value = t.Predecessors
        End If
    Next t
    
    End Sub


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    • Marked as answer by JAMES TENTION Tuesday, February 4, 2014 10:32 PM
    Tuesday, February 4, 2014 9:26 PM
  • Hi James, if you don't know any VBA, this is certainly a good place to learn.

    I'd assumed that you were using the xlsm template as it included formatting or some macro, if you just want to create a new blank spreadsheet, then we just take out the template name (although if you've not got a template, you may be better of using an export map as described in my previous answer).

    Also, to get the predecessor names you need to loop around the task dependencies collection for each task, I've added that to the code as well, along with comments to show you where this is happening.

    Sub ExportToExcel()
    
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim t As Task
    Dim pj As Project
    Dim i As Integer
    Dim td As TaskDependency
    Dim str_Predecessors As String
    
    Set pj = ActiveProject
    Set xlApp = New Excel.Application
    
    xlApp.Visible = True
    
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    
    'Add the project header info in the top 2 rows
    xlSheet.Cells(1, 1).Value = "Project Name"
    xlSheet.Cells(1, 2).Value = pj.Name
    xlSheet.Cells(2, 1).Value = "Project Title"
    xlSheet.Cells(2, 2).Value = pj.Title
    
    'and the column headings in row four
    xlSheet.Cells(4, 1).Value = "Task ID"
    xlSheet.Cells(4, 2).Value = "Task Name"
    xlSheet.Cells(4, 3).Value = "Task Start"
    xlSheet.Cells(4, 4).Value = "Task Finish"
    xlSheet.Cells(4, 5).Value = "Task Notes"
    xlSheet.Cells(4, 6).Value = "Summary Task"
    xlSheet.Cells(4, 7).Value = "Predecessor Tasks"
    
    i = 4
    
    'add the task details from row 5 onwards
    For Each t In pj.Tasks
        If t.Text20 = "Yes" Then
            i = i + 1
            xlSheet.Cells(i, 1).Value = t.ID
            xlSheet.Cells(i, 2).Value = t.Name
            xlSheet.Cells(i, 3).Value = t.Start
            xlSheet.Cells(i, 4).Value = t.Finish
            xlSheet.Cells(i, 5).Value = t.Notes
            xlSheet.Cells(i, 6).Value = t.Summary
            str_Predecessors = ""
            'loop through the dependencies
            For Each td In t.TaskDependencies
                ''getting predecessors only
                If td.To = t Then
                    'build the string with the names of the predecessors
                    'concatenated together with commas.
                    If str_Predecessors = "" Then
                        str_Predecessors = td.From.Name
                    Else
                        str_Predecessors = str_Predecessors & ", " & td.From.Name & ", "
                    End If
                End If
            Next td
            'put the built-up string in the Excel field.
            xlSheet.Cells(i, 7).Value = str_Predecessors
        End If
    Next t
    
    End Sub


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    • Marked as answer by JAMES TENTION Wednesday, February 5, 2014 11:52 PM
    Wednesday, February 5, 2014 9:55 AM

All replies

  • Nearly there, just two things you needed to amend:

    1 - Rather than "Workbooks.Open", use "Workbooks.Add" - this will create a document based on the template, rather than opening the actual template

    2 - Use an integer variable (i) to count the number of tasks that meet your criteria, to determine which row in the spreadsheet you should be using

    See the example below.

    Some people would say this is over-complicated, as you could use a filter to get the tasks that meet your criteria, then a map to do the same export by going into File > Save As, and select "Excel Workbook (*.xlsx)" format. This will allow you to set up a new map which will allow you to select the fields you want in there.

    However, I can understand the need to do it this way if you've got a special template you want to slot the data into (I personally do this a lot).

    Sub ExportToExcel()
    
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim proj As Project
    Dim t As Task
    Dim pj As Project
    Dim i As Integer
    
    Set pj = ActiveProject
    Set xlApp = New Excel.Application
    
    xlApp.Visible = True
    AppActivate "Microsoft Excel"
    
    Set xlBook = xlApp.Workbooks.Add(Template:="C:\Templates\exporttest.xlsm")
    Set xlSheet = xlBook.Worksheets(1)
    
    xlSheet.Cells(1, 1).Value = "Project Name"
    xlSheet.Cells(1, 2).Value = pj.Name
    xlSheet.Cells(2, 1).Value = "Project Title"
    xlSheet.Cells(2, 2).Value = pj.Title
    xlSheet.Cells(4, 1).Value = "Task ID"
    xlSheet.Cells(4, 2).Value = "Task Name"
    xlSheet.Cells(4, 3).Value = "Task Start"
    xlSheet.Cells(4, 4).Value = "Task Finish"
    xlSheet.Cells(4, 5).Value = "Task Notes"
    xlSheet.Cells(4, 6).Value = "Summary Task"
    xlSheet.Cells(4, 7).Value = "Predecessor Tasks"
    
    i = 4
    
    For Each t In pj.Tasks
        If t.Text20 = "Yes" Then
            i = i + 1
            xlSheet.Cells(i, 1).Value = t.ID
            xlSheet.Cells(i, 2).Value = t.Name
            xlSheet.Cells(i, 3).Value = t.Start
            xlSheet.Cells(i, 4).Value = t.Finish
            xlSheet.Cells(i, 5).Value = t.Notes
            xlSheet.Cells(i, 6).Value = t.Summary
            xlSheet.Cells(i, 7).Value = t.Predecessors
        End If
    Next t
    
    End Sub


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    • Marked as answer by JAMES TENTION Tuesday, February 4, 2014 10:32 PM
    Tuesday, February 4, 2014 9:26 PM
  • Thank you very much. That did the trick. However, I still had to add the file to the folder path in order for the script to run without error.

    BTW, I don't know any VBA, this is a script which I believe you posted and I massaged a bit.

    Now I have another question. Does a method exist where I can export the predecessor/successor names instead of the ID of the predecessor/successor. For usage in this export the predecessor/successor will only have one numerical entry each respectively (no FS, SS, etc)

    Thanks

    JT

    Tuesday, February 4, 2014 10:38 PM
  • Hi James, if you don't know any VBA, this is certainly a good place to learn.

    I'd assumed that you were using the xlsm template as it included formatting or some macro, if you just want to create a new blank spreadsheet, then we just take out the template name (although if you've not got a template, you may be better of using an export map as described in my previous answer).

    Also, to get the predecessor names you need to loop around the task dependencies collection for each task, I've added that to the code as well, along with comments to show you where this is happening.

    Sub ExportToExcel()
    
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim t As Task
    Dim pj As Project
    Dim i As Integer
    Dim td As TaskDependency
    Dim str_Predecessors As String
    
    Set pj = ActiveProject
    Set xlApp = New Excel.Application
    
    xlApp.Visible = True
    
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    
    'Add the project header info in the top 2 rows
    xlSheet.Cells(1, 1).Value = "Project Name"
    xlSheet.Cells(1, 2).Value = pj.Name
    xlSheet.Cells(2, 1).Value = "Project Title"
    xlSheet.Cells(2, 2).Value = pj.Title
    
    'and the column headings in row four
    xlSheet.Cells(4, 1).Value = "Task ID"
    xlSheet.Cells(4, 2).Value = "Task Name"
    xlSheet.Cells(4, 3).Value = "Task Start"
    xlSheet.Cells(4, 4).Value = "Task Finish"
    xlSheet.Cells(4, 5).Value = "Task Notes"
    xlSheet.Cells(4, 6).Value = "Summary Task"
    xlSheet.Cells(4, 7).Value = "Predecessor Tasks"
    
    i = 4
    
    'add the task details from row 5 onwards
    For Each t In pj.Tasks
        If t.Text20 = "Yes" Then
            i = i + 1
            xlSheet.Cells(i, 1).Value = t.ID
            xlSheet.Cells(i, 2).Value = t.Name
            xlSheet.Cells(i, 3).Value = t.Start
            xlSheet.Cells(i, 4).Value = t.Finish
            xlSheet.Cells(i, 5).Value = t.Notes
            xlSheet.Cells(i, 6).Value = t.Summary
            str_Predecessors = ""
            'loop through the dependencies
            For Each td In t.TaskDependencies
                ''getting predecessors only
                If td.To = t Then
                    'build the string with the names of the predecessors
                    'concatenated together with commas.
                    If str_Predecessors = "" Then
                        str_Predecessors = td.From.Name
                    Else
                        str_Predecessors = str_Predecessors & ", " & td.From.Name & ", "
                    End If
                End If
            Next td
            'put the built-up string in the Excel field.
            xlSheet.Cells(i, 7).Value = str_Predecessors
        End If
    Next t
    
    End Sub


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    • Marked as answer by JAMES TENTION Wednesday, February 5, 2014 11:52 PM
    Wednesday, February 5, 2014 9:55 AM
  • Alright Andrew

    You are amazing and have been tremendous help.

    So I figured out the usefulness of employing the Template technique (extremely valuable) and created a Template to dump the export (Beautiful), so i'll be retaining the template code.

    I've added the predecessor code which works like a charm.

    Through some additional digging around I found code to make a button on the Navbar and how to envoke the navbar at startup. I've added that, but not sure if it's in the right place (though it still works).

    Question 1: Does all of the VBA code have to be in the same VBA window or can they be separate (not sure if that's clear)?

    Question 2: Does a method exist to ensure this macro persists when I send the file to someone? Just Save and send?

    Here is the code I have so far:

    Private Sub AddCustomNavigation()
    
        Dim myNavBar As String
        
        myNavBar = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
        myNavBar = myNavBar + "  <mso:ribbon>"
        myNavBar = myNavBar + "    <mso:qat/>"
        myNavBar = myNavBar + "    <mso:tabs>"
        myNavBar = myNavBar + "      <mso:tab id=""tools"" label=""UTILITIES"" insertBeforeQ=""mso:TabFormat"">"
      
        myNavBar = myNavBar + "        <mso:group id=""myTools"" label=""MyTools"" autoScale=""true"">"
        myNavBar = myNavBar + "          <mso:button id=""placeholder1"" label=""Export Issue Log"" "
        myNavBar = myNavBar + "imageMso=""DiagramTargetInsertClassic"" onAction=""ExportToExcel""/>"
        myNavBar = myNavBar + "        </mso:group>"
        
        myNavBar = myNavBar + "      </mso:tab>"
        myNavBar = myNavBar + "    </mso:tabs>"
        myNavBar = myNavBar + "  </mso:ribbon>"
        myNavBar = myNavBar + "</mso:customUI>"
        
        ActiveProject.SetCustomUI (myNavBar)
        
    End Sub
    
    Private Sub Project_Open(ByVal pj As Project)
    
    AddCustomNavigation
    
    End Sub
    
    Sub ExportToExcel()
    
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim proj As Project
    Dim t As Task
    Dim pj As Project
    Dim i As Integer
    Dim td As TaskDependency
    Dim str_Predecessors As String
    Set pj = ActiveProject
    Set xlApp = New Excel.Application
    
    xlApp.Visible = True
    AppActivate "Excel"
    
    Set xlBook = xlApp.Workbooks.Add(Template:="E:\Project-Test\ExportTest.xlsx")
    Set xlSheet = xlBook.Worksheets(1)
    
    'Add the project header info in the top 2 rows
    xlSheet.Cells(1, 1).Value = "Project Name: " & pj.Title
    'and the column headings in row four
    xlSheet.Cells(4, 1).Value = "ID"
    xlSheet.Cells(4, 2).Value = "Parent ID"
    xlSheet.Cells(4, 3).Value = "Parent Name"
    xlSheet.Cells(4, 4).Value = "Date Created"
    xlSheet.Cells(4, 5).Value = "Type"
    xlSheet.Cells(4, 6).Value = "Description"
    xlSheet.Cells(4, 7).Value = "Assigned To"
    xlSheet.Cells(4, 8).Value = "Target Date"
    xlSheet.Cells(4, 9).Value = "Date Started"
    xlSheet.Cells(4, 10).Value = "Date Completed"
    xlSheet.Cells(4, 11).Value = "Status"
    xlSheet.Cells(4, 12).Value = "Next Action"
    xlSheet.Cells(4, 13).Value = "Impact"
    xlSheet.Cells(4, 14).Value = "Notes"
    xlSheet.Cells(4, 15).Value = "Summary Task"
    
    i = 4
    
    'add the task details from row 5 onwards
    For Each t In pj.Tasks
        If t.Text20 = "Yes" Then
            i = i + 1
            xlSheet.Cells(i, 1).Value = t.ID
            xlSheet.Cells(i, 2).Value = t.Predecessors
            xlSheet.Cells(i, 4).Value = t.Date6
            xlSheet.Cells(i, 5).Value = t.Text29
            xlSheet.Cells(i, 6).Value = t.Name
            xlSheet.Cells(i, 7).Value = t.ResourceNames
            xlSheet.Cells(i, 8).Value = t.Date5
            xlSheet.Cells(i, 9).Value = t.ActualStart
            xlSheet.Cells(i, 10).Value = t.ActualFinish
            xlSheet.Cells(i, 11).Value = t.Text28
            xlSheet.Cells(i, 12).Value = t.Text27
            xlSheet.Cells(i, 13).Value = t.Text26
            xlSheet.Cells(i, 14).Value = t.Notes
            xlSheet.Cells(i, 15).Value = t.Summary
            str_Predecessors = ""
            'loop through the dependencies
            For Each td In t.TaskDependencies
                ''getting predecessors only
                If td.To = t Then
                    'build the string with the names of the predecessors
                    'concatenated together with commas.
                    If str_Predecessors = "" Then
                        str_Predecessors = td.From.Name
                    Else
                        str_Predecessors = str_Predecessors & ", " & td.From.Name & ", "
                    End If
                End If
            Next td
            'put the built-up string in the Excel field.
            xlSheet.Cells(i, 3).Value = str_Predecessors
        End If
    Next t
    
    End Sub
    

    Wednesday, February 5, 2014 11:52 PM
  • Hello James - glad I could help, and that's very impressive pulling that together.

    Your 2 questions:

    Question 1: Does all of the VBA code have to be in the same VBA window or can they be separate (not sure if that's clear)?

    You can create as many modules as you need in the project. In the screenshot below you see a project I'm working on, and it has multiple modules, and it's best to use a clear naming convention. At the bottom of the screenshot, I've right clicked and highlighted the option to add a new module. One thing to remember, if you want one module to be able to access a routine or function contained within another, then it needs to be declared as an "Public Sub", not private.

    Question 2: Does a method exist to ensure this macro persists when I send the file to someone? Just Save and send?

    In the screenshot, I've highlighted the top section as the global file, and the bottom section as the current project, If you move the code into the current project's ThisProject module rather than the Global file, them it will stay with the file when you send it.

    However, to get your code to work, you had to add in the Reference to the MS Excel library, but this reference is specific to the global file. To get this code to work in the current project, you'll need to select that project, then select Tools > References and add the reference again:


    Andrew Simpson
    Founder – Eversight Ltd

    E: info@eversight.co.uk
    W: www.eversight.co.uk

    Download Eversight for MS Project — a free add-on which helps users build high quality schedules with MS project.

    Thursday, February 6, 2014 11:45 AM
  • Hi Andrew,

    based on the question asked here on this link:

    http://social.technet.microsoft.com/Forums/projectserver/en-US/9c46458c-893f-41ba-bd46-bdb59c533f4a/export-project-tasks-to-excel-using-vba-by-custom-field?forum=project2010custprog

    I tried to use the menu section but I believe the code related to the menu using xml, which I do not know, I think this should be replaced by something else if I am using MS Project 2007 instead.What would you change the following:

    Private Sub AddCustomNavigation()
    
        Dim myNavBar As String
        
        myNavBar = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
        myNavBar = myNavBar + "  <mso:ribbon>"
        myNavBar = myNavBar + "    <mso:qat/>"
        myNavBar = myNavBar + "    <mso:tabs>"
        myNavBar = myNavBar + "      <mso:tab id=""tools"" label=""UTILITIES"" insertBeforeQ=""mso:TabFormat"">"
      
        myNavBar = myNavBar + "        <mso:group id=""myTools"" label=""MyTools"" autoScale=""true"">"
        myNavBar = myNavBar + "          <mso:button id=""placeholder1"" label=""Export Issue Log"" "
        myNavBar = myNavBar + "imageMso=""DiagramTargetInsertClassic"" onAction=""ExportToExcel""/>"
        myNavBar = myNavBar + "        </mso:group>"
        
        myNavBar = myNavBar + "      </mso:tab>"
        myNavBar = myNavBar + "    </mso:tabs>"
        myNavBar = myNavBar + "  </mso:ribbon>"
        myNavBar = myNavBar + "</mso:customUI>"
        
        ActiveProject.SetCustomUI (myNavBar)
        
    End Sub

    It would make sense logically to change the following code to a different one referring to office 2007 isn't it?

    myNavBar = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"

    I did replace it with:

        myNavBar = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2006/01/customui"">"

    But I get the following error:

    Error 438

    Object does not support this property or method

    at the following line:

        
        ActiveProject.SetCustomUI (myNavBar)

    I did install the Schema reference for office 2007 but it still gives me that error. Should a set a reference object for that?

    Regards,

    Chuck

    Tuesday, May 27, 2014 3:22 PM
  • Users,

    This question has been posted to a new thread on this forum (http://social.technet.microsoft.com/Forums/projectserver/en-US/c9477237-c964-4a39-b16e-3cd4a21a7e5e/export-project-tasks-to-excel-using-vba-by-custom-field?forum=project2010custprog). Since dual posting is frowned upon, please check that thread.

    John

    Tuesday, May 27, 2014 4:57 PM