Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    export to excel - monthly resource issues (2000)

    I am creating a chart in Excel with details of which projects / tasks that a resource is working on. e.g. from now til the end of the year the training dept has 3 trainers working on project A, 2 on project b and 1 on project C, but project C finishes at the end of October. This is a master project plan with approx. 50 smaller tasks linked to it.
    I have taken over the start and finish dates to Excel, but this will only show me totals. I need to break it down to show for October we have 6 trainers working on 3 project, November and December should show 5 trainers on 2 projects.
    I can't find a column for Month?
    The Task Usage report was helpful, but I can't seem to add the project names to this - nor can I send this to Excel.
    If you know how to get the monthly breakdown into Excel, or know how to send the task usage report to Excel I would appreciate your help.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: export to excel - monthly resource issues (2000)

    There is no easy way to export timephased data to excel for analysis. The export maps give you a lot of data, but not timephased.

    If using the Task or Resource usage view, you could cut and paste the data to excel. (You have to do this twice, once on each side and then make sure the data lines up.) But this is a little clunky. Let me see if I can find some code to help you out.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: export to excel - monthly resource issues (2000)

    The following code exports timephased data to excel. Just create a macro in Project and insert this code. Then run it.

    <pre>Sub ExportWorkData()

    ' Export Work Data Macro obtained from
    ' Special Edition using MS Project 2000
    ' by Tim Pyron, et al.

    Dim xlApp As Excel.Application
    Dim xlbook As Excel.Workbook
    Dim xlrng As Excel.Range
    Dim Proj As Project
    'Dim T As Task
    Dim R As Resource
    Dim A As Assignment
    Dim TSV As TimeScaleValues
    Dim Weeknumber As Integer
    Dim DatesAdded As Boolean

    'Set xlApp = GetObject(, "Excel.Application")

    If xlApp Is Nothing Then
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    End If

    AppActivate "Microsoft Excel"

    Set xlbook = xlApp.Workbooks.Add

    Set xlrng = xlApp.ActiveCell

    Set Proj = ActiveProject
    xlrng = "Weekly Work Report for " & Proj.Name
    xlrng.Range("A2") = "As of " & Format(Date, "mmmm dd yyyy")
    xlrng.Range("A1:A2").Font.Bold = True
    xlrng.Font.Size = 14

    Set xlrng = xlrng.Offset(3, 0)

    xlrng = "Task Name"
    With xlrng.EntireRow
    .Font.Bold = True
    .WrapText = True
    .HorizontalAlignment = xlHAlignCenter
    .VerticalAlignment = xlVAlignCenter
    .AutoFit
    End With

    xlrng.EntireColumn.ColumnWidth = 35
    Set xlrng = xlrng.Offset(1, 0)

    For Each T In Proj.Tasks
    ' For Each A In T.Assignments
    ' MsgBox A.ResourceName
    ' Next


    If Not (T Is Nothing) Then
    xlrng = T.Name
    Set TSV = T.TimeScaleData(Proj.ProjectStart, Proj.ProjectFinish, pjTaskTimescaledWork, pjTimescaleWeeks, 1)

    If DatesAdded = False Then
    For Weeknumber = 1 To TSV.count
    xlrng.Offset(-1, Weeknumber) = TSV(Weeknumber).EndDate
    Next
    xlrng.Offset(-1, 0).EntireRow.NumberFormat = "mm/dd/yyyy"
    DatesAdded = True
    End If
    For Weeknumber = 1 To TSV.count
    xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60
    Next

    If T.Summary = True Then
    xlrng.EntireRow.Font.Bold = True
    End If
    Set xlrng = xlrng.Offset(1, 0)
    End If
    Next

    xlrng.Offset(-1, 0).CurrentRegion.Offset(1, 0).NumberFormat = "0.00h;;"

    End Sub
    </pre>

    Regards,

    Gary
    (It's been a while!)

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: export to excel - monthly resource issues (2000)

    Gary, I've been playing around with this code and keep getting an error at the following line...
    xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60

    I get type mismatch. I'm wondering - what is the intent of that line... what are you attempting to insert in to the cell at that point?

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: export to excel - monthly resource issues (2000)

    The intent of that line is to get the hours data associated with a resource and write that data for the particular task and timeframe. If there are no resources associated with a task, then TSV(Weeknumber) = "" and you can't do the calculation ""/60.

    Insert the following if then statement around the statement giving you an error to bypass this.

    <pre>If TSV(Weeknumber) <> "" Then
    xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60
    End If</pre>



    HTH
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: export to excel - monthly resource issues (2000)

    OK, that makes sense though I did something a little different.

    If TSV(Weeknumber) = ""
    xlrng.Offset(0, Weeknumber) = "no data"
    Else
    xlrng.Offset(0, Weeknumber) = TSV(Weeknumber) / 60
    end if

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: export to excel - monthly resource issues (2000)

    Did the code get what you needed to excel?

    Out of curiousity, does the "no data" give you an error when you execute the code.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: export to excel - monthly resource issues (2000)

    The code got what I needed to Excel. Though I'm second guessing myself on inserting "No data" it might be redundant.

    I don't get an error when executing the code.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •