none
Export schedule MS PROJECT 2010 to Excel in VBA RRS feed

  • Question

  • I have a big problem, and I can not solve.

    I have a schedule made ​​in the PROJECT and must export it to Excel via VBA code.

    I have a spreadsheet template, since this spreadsheet I have some modules that will handle such data and tals.

    My biggest problem is that I am not able to read the records or the titles of the project.

    Thanks



    Hezequias Vasconcelos

    Friday, April 27, 2012 11:26 AM

Answers

  • In  that case - you'll need something like the examlpe below

    Make sure that Microsoft Excel is included in the References though: From VBE, Tools > References and select "Microsoft Excel Object Library" for the version you're working with.

    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:\Temp\Template.xls")
    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"
    For Each t In pj.Tasks
        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
    Next t
    End Sub

    Friday, April 27, 2012 1:14 PM

All replies

  • Hello - not quite clear on the question, so i've made a few assumptions and hope my answer is useful. Assuming:
     - You want to execute the code from MS Project
     - you want to export the currently active project
     - You've already worked out how to open your Excel template from project
     - By records, you mean tasks

    Q1 - "Project Titles"
    You get the project title information from the project object. The code below gets the project title info and outputs it to the debug window.

    Dim pj As Project Set pj = ActiveProject Debug.Print "Project Name: " & ActiveProject.Name 'gives the file name Debug.Print "Project Title: " & ActiveProject.Title 'gives the title



    Q2 - "Project Records"
    To get the records (assuming you mean tasks) you then do a for/next loop through the task objects in your project. This code loops theough the tasks and outputs the data found to the debug window:

    Dim t As Task Dim pj As Project Set pj = ActiveProject For Each t In pj.Tasks Debug.Print Debug.Print " Task Name: " & t.Name 'gives the task name Debug.Print " Task ID: " & t.ID 'gives the task id Next t


    Hope this helps,
    Andrew


    • Edited by Andrew Simpson Friday, April 27, 2012 12:04 PM Last line of code truncated. Added carriage return to end
    Friday, April 27, 2012 12:03 PM
  • Hi Andrew,

    My considerations.

    Hello - not quite clear on the question, so i've made a few assumptions and hope my answer is useful. Assuming:
     - You want to execute the code from MS Project - YES
     - you want to export the currently active project - YES
     - You've already worked out how to open your Excel template from project - NO, and I do not know how to do.
     - By records, you mean tasks - YES

    Thanks


    Hezequias Vasconcelos

    Friday, April 27, 2012 12:34 PM
  • In  that case - you'll need something like the examlpe below

    Make sure that Microsoft Excel is included in the References though: From VBE, Tools > References and select "Microsoft Excel Object Library" for the version you're working with.

    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:\Temp\Template.xls")
    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"
    For Each t In pj.Tasks
        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
    Next t
    End Sub

    Friday, April 27, 2012 1:14 PM
  • Hi Andrew

    Perfect.
    Thank you for the help I can not thank you.


    Hezequias Vasconcelos

    Friday, April 27, 2012 1:37 PM
  • No worries - thanks for the feedback.
    Monday, April 30, 2012 10:00 AM
  • Hi Andrew, 

    Is it possible for this to extract multiple .mpp data into one excel tab?

    So maybe call from excel, a dialogue box where you can select multiple .mpp files and loop through each one and populate a single excel worksheet?

    Regards

    Sean

    Friday, August 19, 2016 1:14 AM