Exporting resource Usage view to Excel

Home Forums Microsoft Project Discussion Forum Exporting resource Usage view to Excel

  • This topic is empty.
Viewing 2 reply threads
  • Author
    Posts
    • #25223
      samg54
      Guest

      Hi,

      I am trying to export data from the resource usage view in microsoft project into excel.
      I am using Microsoft Project 2007 and Excel 2007.

      I can export the left hand side of the resource usage screen, but i cannot export the time phased values in the right hand side of the screen.

      I have the code below which runs in Project, and was wondering if anyone could help me with this
      The code below exports the left hand side of the screen without any problems, however the data on the right hand side does not export.

      ##########
      Sub Export_to_Excel()

      ‘Variables
      Dim xlRange as Excel.Range
      Dim xlApp As Excel.Application
      Dim xlWkb As Excel.Workbook
      Dim xlSheet As Excel.Worksheet

      ‘Open Excel
      Set xlApp = New Excel.Application
      xlApp.Visible = True
      AppActivate “Microsoft Excel”
      Set xlWkb = xlApp.Workbooks.Add

      ‘###################################
      ‘EXPORTS RESOURCE USAGE – FIRST HALF
      ‘###################################

      ‘Copy first half of resource usage data
      ViewApply Name:=”Resource Usage”
      SelectResourceColumn Column:=”Name”, Additional:=1
      OutlineShowAllTasks
      EditCopy

      ‘Selects where to add new data
      Set xlSheet = xlWkb.Worksheets(“Sheet1”)
      xlSheet.Activate
      Set xlRange = xlSheet.Range(“A2:A2″)
      xlRange.Select

      ‘Paste data into Excel
      xlRange.PasteSpecial Paste:=xlPasteValues
      ‘###################################
      ‘EXPORTS RESOURCE USAGE – SECOND HALF – not working, need to resolve
      ‘###################################

      ‘Copy second half of resource usage data
      ViewApply Name:=”Resource Usage”
      PaneNext
      SelectTimescaleRange Row:=1, StartTime:=”Mon 29/07/13 00:00″, Width:=13, Height:=10
      EditCopy

      ‘Selects where to add new data
      Set xlSheet = xlWkb.Worksheets(“Sheet1”)
      xlSheet.Activate
      Set xlRange = xlSheet.Range(“G2:G2”)
      xlRange.Select

      ‘Paste data into Excel
      xlRange.PasteSpecial Paste:=xlPasteValues

      End Sub

      ##########

      NOTE: I have already turned on the VBA project reference ‘Microsoft Excel 12.0 Object Library’.

      Thanks in advance for any help.
      Sam.

    • #25807
      Ben Wiegers
      Participant

      Sam,

      why don’t you create a visual report for Resource Usage data (make sure you set the depth to days)?
      That way you’ll create a pivot table with all the information you will find in the Resource Usage sheet.

      Best of luck,

      Ben Wiegers

       

    • #309053
      saeideh
      Guest

      Hi.
      I am trying to save a view in ms project into excel.
      i want resource name form into excel. how do I save a view into excel???

Viewing 2 reply threads
  • You must be logged in to reply to this topic.