Export schedule MS PROJECT 2010 to Excel in VBA

Toate mesajele

• 27 aprilie 2012 12:03

   

   
  

  1

  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

  
  

  • Editat de Andrew Simpson 27 aprilie 2012 12:04 Last line of code truncated. Added carriage return to end
  •  
  • Mesaj
  • Citare

   
• 27 aprilie 2012 12:34

   

   
  

  0

  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

  • Mesaj
  • Citare

   
• 27 aprilie 2012 13:14

   

   
  

  1

  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

  
  

  • Marcat ca răspuns de Hezequias Vasconcelos 27 aprilie 2012 13:37
  •  
  • Mesaj
  • Citare

   
• 27 aprilie 2012 13:37

   

   
  

  0

  Hi Andrew

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

  ---

  Hezequias Vasconcelos

  • Mesaj
  • Citare

   
• 30 aprilie 2012 10:00

   

   
  

  0
  No worries - thanks for the feedback.
  • Mesaj
  • Citare