Loading...
Quick Links

Exporting resource Usage view to Excel

Home Forums Discussion Exporting resource Usage view to Excel

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #25223 Reply
    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 Reply
    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 Reply
    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 3 posts - 1 through 3 (of 3 total)
Reply To: Exporting resource Usage view to Excel
Your information:




12 + = 19